Plan Guides im SQL Server: Abfragen optimieren, die Du gar nicht ändern kannst

Plan Guides haben einen mäßigen Ruf. Zu fummelig, zu leicht falsch gesetzt, und wenn sie stillschweigend nicht mehr greifen, sucht man den Grund halbe Nachmittage. An dem Ruf ist etwas dran. Trotzdem sind sie in bestimmten Situationen das einzige Werkzeug, das übrig bleibt, und eine dieser Situationen begegnet uns bei Sage-100-Kunden immer wieder. Deshalb dieser Beitrag.

Das Muster ist fast immer gleich. Gestern lief die Rechnungsauskunft normal, heute steht sie. Kein Update, kein neuer Code, niemand hat etwas angefasst. Trotzdem braucht dieselbe Abfrage plötzlich das Zehnfache. Oft genug liegt es am Ausführungsplan, den sich der SQL Server neu zusammengebaut hat, diesmal falsch. Es gibt andere Ursachen, Blocking, einen I/O-Engpass, eine gekippte Statistik, aber ein entgleister Plan gehört zu den häufigsten. Läge die Abfrage in einer eigenen Prozedur, würdest Du einen Hint dranschreiben und fertig. Bei einer Standardsoftware wie Sage 100 gehört der SQL-Text aber der Anwendung. Du kommst an die Quelle nicht ran. Genau dafür wurden Plan Guides gebaut.

Kurz gesagt: Was ein Plan Guide macht

Ein Plan Guide hängt einen Query Hint von außen an eine Abfrage. Die Anwendung schickt weiter exakt dasselbe SQL, aber der Server behandelt es beim Kompilieren so, als stünde Dein OPTION (...) mit im Text. Der Hersteller merkt nichts davon, das nächste Update überschreibt nichts, und Du hast trotzdem die Kontrolle über die Strategie. Microsoft nennt in der Dokumentation zu Planhinweislisten genau diesen Fall als Einsatzzweck: Wenn Du den Text der Abfrage nicht ändern möchtest oder kannst, etwa bei einer Drittanbieteranwendung.

Es gibt drei Typen. SQL für einzelne Anweisungen, die als Ad-hoc-Statement oder über sp_executesql hereinkommen, und das ist bei ERP-Systemen mit Abstand der häufigste Fall. OBJECT für Statements innerhalb einer Prozedur, Funktion oder eines Triggers. Und TEMPLATE, das steuert die Parametrisierung selbst und wird so selten gebraucht, dass Du es vermutlich nie anfassen wirst. Für den Rest dieses Beitrags geht es um den SQL-Typ.

Warum der Optimizer überhaupt danebengreift

Wichtig zum Verständnis: Der Server baut den Plan einmal und verwendet ihn danach wieder. Beim Kompilieren schaut er sich den konkreten Parameterwert an, der gerade anliegt, und optimiert den Plan anhand der Statistiken genau für diesen einen Wert. Dieses Erschnüffeln nennt sich Parameter Sniffing. Solange die folgenden Aufrufe ähnlich viele Zeilen treffen, geht das gut. Weichen sie stark ab, passt der einmal gewählte Plan nicht mehr. Die üblichen Auslöser:

• Ungleich verteilte Daten. Der beim ersten Aufruf gesniffte Wert traf eine Handvoll Zeilen, der nächste trifft Hunderttausende. Der Plan bleibt aber der alte. Genau das sehen wir gleich im Beispiel.
• Schiefe oder veraltete Statistiken, typischerweise nach einem großen Import oder einer Migration. Die hinterlegte Verteilung stimmt nicht mehr, der Optimizer verschätzt sich um Größenordnungen.
• Plan-Regression nach einem Wechsel des Compatibility Levels oder des Cardinality Estimators. Eine Abfrage, die seit Jahren brav lief, bekommt auf einmal einen anderen Plan.

In all diesen Fällen weißt Du oft genau, welchen Plan der Server nehmen sollte. Sagen kannst Du es ihm nur nicht, weil die Quelle nicht Dir gehört. Der Plan Guide schließt diese Lücke.

Ein echter Fall aus einer Sage-100-Datenbank

Am konkreten Beispiel wird es greifbarer. Die Belegtabelle KHKVKBelege einer Produktivdatenbank, rund 1,7 Millionen Verkaufsbelege, auf der Spalte Belegkennzeichen ein schmaler nicht gruppierter Index. Diese Spalte ist grotesk ungleich gefüllt:

Belegkennzeichen    Anzahl Belege
----------------    -------------
VFR                       632.848
VLL                       497.705
VVA                       214.380
...
VSR                           193
VFT                           174

Das häufigste Kennzeichen macht allein gut ein Drittel der Tabelle aus, das seltenste kommt nicht mal 200 Mal vor. Für Parameter Sniffing gibt es kaum eine schönere Ausgangslage. Die Anwendung schickt eine parametrisierte Abfrage, im Kern diese hier:

exec sp_executesql
  N'SELECT BelID, Belegnummer, Belegdatum, A0Name1, Nettobetrag
    FROM KHKVKBelege
    WHERE Belegkennzeichen = @kz AND Mandant = @m',
  N'@kz varchar(3), @m smallint',
  @kz = 'VFT', @m = 1

Kompiliert der Server das zum ersten Mal für ein seltenes Kennzeichen wie VFT, schätzt er eine Handvoll Treffer und nimmt einen Index Seek mit Key Lookup. Für 174 Zeilen ist das goldrichtig, da gibt es nichts zu meckern. Nachgemessen an den logischen Lesevorgängen, also den 8-KB-Seiten, die der Server anfassen muss:

Die dritte Zeile ist der Schaden. Der zwischengespeicherte Seek-Plan trifft auf das häufige Kennzeichen und macht brav für jeden der 630.000 Treffer einen einzelnen Key Lookup in die Haupttabelle. Aus einem sauberen Scan mit 464.000 Lesevorgängen werden knapp sieben Millionen, Faktor fünfzehn. Im Arbeitsalltag ist das der Unterschied zwischen einer Auskunft, die aufgeht, und einem Fenster, das einfach steht.

Das Perfide daran: Aus Anwendersicht wirkt es wie Zufall, ob Du in den GAU läufst. In Wahrheit hängt es daran, welcher Parameterwert bei der nächsten Kompilierung anliegt, also nach einem Neustart, einem Statistik-Update oder wenn der Plan aus dem Cache geflogen ist. Findest Du den Übeltäter über den Plan Cache oder den Query Store, siehst Du es sofort am verräterischen Missverhältnis im Plan: Geschätzte 174 Zeilen, tatsächlich 632.848. Wenn Estimated und Actual so weit auseinanderliegen, ist Sniffing fast immer die Antwort.

Der Plan Guide gegen das Sniffing

Die naheliegende Reaktion ist, dem Server das Sniffing abzugewöhnen und ihn mit der Durchschnittsverteilung rechnen zu lassen. Der Hint dafür heißt OPTIMIZE FOR UNKNOWN. Klingt nach der sauberen Lösung. Ist sie in diesem Fall aber nicht, und das sieht man erst, wenn man nachmisst. Die Durchschnittsschätzung landet hier bei rund 122.000 Zeilen, und mit dieser Schätzung bleibt der Optimizer trotzdem beim Index Seek mit Key Lookup, statt auf den Scan zu wechseln:

Was hier wirklich zieht, ist OPTION (RECOMPILE). Damit baut der Server bei jedem Aufruf einen frischen Plan, sniffed den tatsächlich übergebenen Wert und liefert pro Wert das Optimum: Den Seek für VFT mit 719 Lesevorgängen, den Scan für VFR mit 464.056. Kein einziger Ausreißer mehr. In den Anwendungscode können wir den Hint nicht schreiben, also hängen wir ihn per Plan Guide an. Genau dieses Muster zeigt übrigens auch die Microsoft-Dokumentation als Beispiel:

EXEC sp_create_plan_guide
    @name   = N'PG_Belege_Belegkennzeichen',
    @stmt   = N'SELECT BelID, Belegnummer, Belegdatum, A0Name1, Nettobetrag
    FROM KHKVKBelege
    WHERE Belegkennzeichen = @kz AND Mandant = @m',
    @type   = N'SQL',
    @module_or_batch = NULL,
    @params = N'@kz varchar(3), @m smallint',
    @hints  = N'OPTION (RECOMPILE)';

Der Preis ist eine Kompilierung pro Ausführung. Bei dieser Auskunft, die ein Sachbearbeiter ein paar Mal am Tag aufruft, fällt das nicht ins Gewicht. Läuft eine Abfrage dagegen zehntausendfach pro Stunde mit demselben Wert, willst Du nicht jedes Mal neu kompilieren. Dann ist der bessere Weg, gezielt den guten Plan festzunageln, also den Scan, der für den häufigen Fall ideal und für die seltenen Werte gerade noch in Ordnung ist. Aber als robuster Startpunkt ist RECOMPILE hier goldrichtig.

Und jetzt der nervige Teil, der die meisten Plan Guides scheitern lässt. Der Text in @stmt muss zeichengenau dem entsprechen, was die Anwendung schickt. Die Doku ist da unmissverständlich: SQL Server vergleicht Zeichen für Zeichen, ohne jede Normalisierung. Jedes Leerzeichen, jeder Zeilenumbruch, jede Klammer. Ein Tippfehler, und der Guide liegt nutzlos herum, ohne dass Dich irgendwer warnt. Also nicht abtippen, sondern den exakten Text aus dem Plan Cache holen, über sys.dm_exec_query_stats zusammen mit sys.dm_exec_sql_text.

Nicht verwechseln solltest Du das mit sp_create_plan_guide_from_handle. Diese Prozedur macht etwas anderes: Sie friert einen bereits im Cache liegenden Plan ein, im Kern wie ein USE PLAN. Praktisch, wenn Du einen konkreten guten Plan dauerhaft festhalten willst. Für unseren Sniffing-Fall aber mit Vorsicht zu genießen, denn wenn gerade der katastrophale Seek-Plan im Cache liegt und Du ihn erwischst, nagelst Du genau den fest. Zum reinen Ermitteln des Statement-Texts taugt sie nicht, dafür sind die beiden DMVs oben da.

Ob der Guide gültig ist, sagt Dir diese Abfrage. Kommt kein Ergebnis zurück, passt es:

SELECT * FROM sys.fn_validate_plan_guide(
    (SELECT plan_guide_id FROM sys.plan_guides
     WHERE name = N'PG_Belege_Belegkennzeichen'));

Gültig heißt aber noch nicht angewendet. Ob er wirklich greift, verrät der Ausführungsplan: Im Eigenschaftenfenster (F4 auf dem Wurzeloperator) muss das Attribut PlanGuideName mit Deinem Namen auftauchen. Erst dann glaube ich es.

Neuere Versionen machen es einfacher

Der klassische Plan Guide ist alt und ein bisschen sperrig, und Microsoft hat das Drumherum inzwischen deutlich komfortabler gemacht. Auf welcher SQL-Server-Version was geht, ändert sich von Release zu Release, deshalb hier nur die groben Linien und für die Details der Verweis auf die offizielle Doku.

• Willst Du bloß einen bekannten guten Plan festhalten, geht das über den Query Store per Plan-Forcing bequemer als über einen Plan Guide, ganz ohne die heikle Textzuordnung.
• Willst Du einen Hint setzen, ohne den Code zu ändern, gibt es seit SQL Server 2022 die Query Store Hints. Microsoft empfiehlt sie in der Doku inzwischen ausdrücklich als den einfacheren Weg gegenüber Plan Guides.
• Und die Parameter Sensitive Plan Optimization kann genau die Art von Schieflage, die wir oben hatten, auf neueren Versionen teils von selbst entschärfen, indem sie je nach Parameterwert unterschiedliche Pläne vorhält.

Für welche Version welches dieser Werkzeuge verfügbar und sinnvoll ist, schaust Du am besten tagesaktuell in der Microsoft-Dokumentation nach. Der klassische Plan Guide bleibt trotzdem relevant: Auf älteren Versionen, für OBJECT- und TEMPLATE-Fälle, und immer dann, wenn die bequemeren Wege für Deine Situation nicht greifen.

Bei alldem ein Wort der Warnung, das mir wichtig ist. Ein erzwungener Plan oder Hint ist eine Krücke, keine Heilung. Er ist goldrichtig, wenn Du an die Quelle nicht rankommst. Aber er versteinert eine Entscheidung, die der Optimizer sonst laufend an neue Datenmengen anpasst. Also: Jeden Guide dokumentieren, nach jedem Anwendungsupdate erneut prüfen, und ihn wieder rausschmeißen, sobald die eigentliche Ursache behoben ist. Der vergessene Plan Guide, der seit drei Jahren einen längst überholten Plan festhält, ist einer der undankbarsten Fehler überhaupt, weil ihn niemand mehr auf dem Schirm hat.

Fazit

Plan Guides sehen nach Nischenwissen aus, aber sie geben Dir die Kontrolle über Abfragen zurück, die Du eigentlich nicht anfassen darfst, und bei einer Standardsoftware wie Sage 100 ist das der Normalfall, nicht die Ausnahme. Unser Belegbeispiel zeigt zweierlei: Den Hebel, aus fast sieben Millionen Lesevorgängen werden mit dem richtigen Hint wieder ein paar Hunderttausend. Und die Mahnung, erst zu messen. Der auf den ersten Blick naheliegende OPTIMIZE FOR UNKNOWN hätte hier nur die Hälfte gebracht, gemessen wurde es besser mit RECOMPILE. Welchen Plan der Server warum nimmt, versteht man nicht durch Raten, sondern durch Nachschauen.

Falls bei Euch so eine Abfrage regelmäßig entgleist und keiner den Grund findet: Das ist genau die Sorte Problem, die wir gern übernehmen. Meldet Euch.