Optimizer Hints

Was sind Optimizer Hints?

Normalerweise wird der SQL Server, wenn ihr eine Abfrage abschickt versuchen, diese Abfrage zu interpretieren und basierend unter anderem auf Statistiken einen möglichst guten Ausführungsplan für eure Abfrage zu finden. Im Normalfall klappt das meistens auch sehr gut. Gelegentlich gibt es aber Fälle, wo der SQL Server sich verschätzt, sei es aufgrund fehlender oder veralteter Statistiken oder weil er die Abfrage falsch interpretiert oder aus irgendwelchen anderen Gründen eine falsche Abschätzung des Aufwands der Abfrage trifft (wer mehr hierzu wissen möchte, der sollte beispielsweise ein PASS Essential des Großmeisters Ricken-San besuchen.

Wie könnt ihr Optimizer Hints verwenden?

Grundsätzlich werden Vorschläge oder Hints für den Optimizer am Ende der Abfrage angegeben. Die Angabe erfolgt dabei durch das Schlüsselwort OPTION gefolgt von Klammern in denen die verschiedenen Optionen für die Abfrage stehen, wovon USE HINT gefolgt von Klammern in denen durch Komma getrennt die Optimierer-Hinweise als Zeichenfolgen stehen. Wenn ihr gerne wissen möchtet, welche Hints auf eurem System möglich sind, dann hilft euch die folgende Abfrage weiter:

SELECT [name] 
FROM sys.dm_exec_valid_use_hints

Diese in SQL Server 2017 hinzugekommene System-View listet euch alle möglichen Abfragehinweise auf, die in eurer Datenbank verfügbar sind. Das ist heute eine Teilmenge der folgenden Attribute:

• ASSUME_JOIN_PREDICATE_DEPENDS_ON_FILTERS: wenn ihr diesen Hinweis verändert die Art, wie der SQL Server die Anzahl von Zeilen in JOIN Statements schätzt. Konkret geht es darum, dass der SQL Server seit der Version 2014 nicht unbedingt annimmt, dass Werte, die in einem JOIN-Prädikat auftreten auch wirklich in beiden Tabellen vorhanden sein müssen, diese Annahme lässt sich mit diesem Hinweis überschreiben.
• ASSUME_MIN_SELECTIVITY_FOR_FILTER_ESTIMATES: Grundsätzlich nimmt der SQL Server an, dass bei mehreren aufeinanderfolgenden Filtern diese voneinander unabhängig sind. Das bedeutet, dass er davon ausgeht, dass ein Filter, der auf die Gesamttabelle 50% der Daten filtert das immer tut, auch wenn vorher bereits ein äquivalenter Filter gesetzt wurde. Deshalb kann es zu Situationen kommen, wo der SQL Server die Anzahl Zeilen massiv unterschätzt. Das lässt sich mit diesem Hinweis verhindern.
• DISABLE_BATCH_MODE_ADAPTIVE_JOINS: Seit dem SQL Server 2017 kann der SQL Server bei Joins die Entscheidung, welcher Join der richtige für eine Abfrage ist, bis nach dem ersten Table Scan verzögern kann. Dieser Hinweis unterbindet dieses Verhalten.
• DISABLE_BATCH_MODE_MEMORY_GRANT_FEEDBACK und DISABLE_ROW_MODE_MEMORY_GRANT_FEEDBACK: Der SQL Server hat in Version 2017 die Möglichkeit, sich den benötigten Speicher für eine Abfrage zu merken und falls zu viel oder zu wenig Speicher reserviert wurde, diesen beim nächsten Ausführen derselben Abfrage entsprechend anzupassen. Das kann zu unerwünschten Nebeneffekten führen und deshalb mit diesen Hinweisen unterbunden werden.
• DISABLE_DEFERRED_COMPILATION_TV: Mit diesem Hinweis steuert ihr, wann der SQL Server Table Valued Variablen auflöst.
• DISABLE_INTERLEAVED_EXECUTION_TVF: Hier geht es darum, wie der SQL Server bei einer Funktion, die eine Tabelle zurückgibt und sich aus mehreren Abfragen zusammensetzt die Anzahl von Zeilen schätzt.
• DISABLE_OPTIMIZED_NESTED_LOOP: Hiermit könnt ihr unterbinden, dass im Abfrageplan eine SORT-Operation verwendet wird, um einen NESTED LOOP JOIN zu optimieren.
• DISABLE_OPTIMIZER_ROWGOAL: Wenn eure Abfrage beispielsweise ein TOP enthält, dann modifiziert das die Anzahl Zeilen, die erwartet wird und der Query Optimizer stellt sich üblicherweise darauf ein. Mit diesem Hinweis, könnt ihr den Query Optimizer anweisen, Konstrukte zu ignorieren, die die Anzahl der zurückgelieferten Zeilen künstlich reduzieren (darunter fällt auch beispielsweise ein OPTION FAST 1000).
• DISABLE_PARAMETER_SNIFFING: Normalerweise versucht der SQL Server bei Abfragen mit Parametern, anhand des ersten übergebenen Paramters den Abfrageplan für alle späteren Parameter festzulegen. Das kann mit dieser Option verhindert werden.
• DISABLE_TSQL_SCALAR_UDF_INLINING: Es geht hierbei darum, dass der SQL Server bei der Version ab 2019 versucht, Funktionen aufzulösen und wenn sie zu teuer werden, direkt in die Abfrage zu übernehmen als skalare Ausdrücke oder als Unterabfragen. Diese Optimierung kann mit diesem Hinweis abgeschaltet werden.
• DISALLOW_BATCH_MODE: Dieser Hinweis schaltet den Batch Mode, den der SQL Server für Column Store Indexe verwendet, für die Abfrage komplett ab.
• ENABLE_HIST_AMENDMENT_FOR_ASC_KEYS: Die Verwendung dieser Option erlaubt es dem SQL Server, für führende Index-Spalten in der Abfrage eine schnelle Statistik aus dem Historgram der Spalte zu erstellen. Das erhöht natürlich die Compile-Zeit der Abfrage, verringert die Abfrage-Zeit aber unter Umständen deutlich.
• ENABLE_QUERY_OPTIMIZER_HOTFIXES: Steuert, ob in der Abfrage neue Änderungen im Query Optimizer verwendet werden dürfen.
• FORCE_DEFAULT_CARDINALITY_ESTIMATION und FORCE_LEGACY_CARDINALITY_ESTIMATION: Diese beiden Hinweise überschreiben das jeweilige Verhalten der Datenbank, nach welcher Methode die Zeilenzahlen in den Tabellen geschätzt werden.
• QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_n: hiermit könnt ihr den Query Optimizer dazu bringen, sich wie in einer bestimmten Version des SQL Server zu verhalten.
• QUERY_PLAN_PROFILE: Diese Option steuert, wie die Abfrage im Monitoring mit Extended Events und dem Profiler zu sehen ist.

Ihr seht, dass ihr viele Möglichkeiten habt, das Verhalten des SQL Servers für einzelne Abfragen zu steuern. Grundsätzlich gilt aber: genauso wie im Dojo der Meister sich nur selten irrt und ihr deshalb davon ausgehen solltet, dass er recht hat, könnt ihr in der großen Mehrzahl der Fälle davon ausgehen, dass der Query Optimizer recht hat und den richtigen Ausführungsplan auswählt. Ihr solltet ihn daher nur in Ausnahmefällen dazu überreden, etwas anderes zu tun, als er eigentlich möchte.

Referenzen

• Offizielle Dokumentation von Microsoft