In den letzten Wochen bin ich immer wieder auf Abfragen aus Applikationen gestoßen, die als Abfragehinweise OPTIMIZE FOR UNKNOWN verwendet haben. In den untersuchten Applikationen wird diese Technik “inflationär” benutzt. Leider haben die Programmierer bei der Verwendung nicht berücksichtigt, wie schnell diese Option eine Abfrage so schlecht machen kann, dass sie nicht mehr in 2 Sekunden sondern in 7 Minuten ausgeführt wird.
Inhaltsverzeichnis
OPTIMIZE FOR UNKNOWN
Der Abfragehinweis kann seit Microsoft SQL Server 2008 verwendet werden, um die Problematik von “Parameter Sniffing” etwas zu entschärfen. Bei der Verwendung von OPTIMIZE FOR UNKNOWN verwendet Microsoft SQL Server nicht mehr das Histogramm eines Statistikobjekts sondern den Density Vektor. Der Density Vektor ist das Verhältnis von eindeutigen Werten in der angegebenen Spalte oder einer Gruppe von Spalten.
Density Vektor
Das folgende Beispiel zeigt, wie der Density Vektor berechnet wird.
USE tempdb; GO SELECT * INTO dbo.messages FROM sys.messages; GO CREATE NONCLUSTERED INDEX nix_messages_severity ON dbo.messages (severity); GO DBCC SHOW_STATISTICS(N'dbo.messages', N'nix_messages_severity'); GO
Die Abbildung zeigt die verschiedenen Elemente eines Statistikobjekts. Aus dem Header kann man entnehmen, dass es 16 unterschiedliche Werte im Index gibt. Der Density Vektor errechnet sich aus der Formel 1/16 = 0,0625. Dieser Faktor kann anschließend verwendet werden, um die durchschnittliche Anzahl von Datensätzen pro einzelnem Indexwert zu ermitteln. Bei insgesamt 290.686 Datensätzen ergibt sich somit eine Verteilung von 18167,875 Datensätzen pro einzelnen Indexwert.
Anwendungsgebiete
Die Option OPTIMIZE FOR UNKNOWN kann als Korrektiv für Parameter Sniffing verwendet werden. Ebenfalls “kann” es bei einem Problem helfen, dass sich ASCENDING KEY nennt. Durch die Verwendung von OPTIMIZE FOR UNKNOWN wird auf die Verwendung des Histogramms vollständig verzichtet und Microsoft SQL Server nimmt immer den Durchschnittswert für alle Indexwerte an.
Hinweis: Alle nachfolgenden Beispiele basieren auf dem neuen CE ab Microsoft SQL Server 2014!
Beispiel 1
Das nachfolgende Beispiel zeigt, welche Unterschiede sich bei der Verwendung von OPTIMIZE FOR UNKNOWN ergeben.
DECLARE @stmt_known NVARCHAR(1024) = N'SELECT * FROM dbo.messages WHERE severity = @S;'; DECLARE @stmt_unknown NVARCHAR(1024) = N'SELECT * FROM dbo.messages WHERE severity = @S OPTION (OPTIMIZE FOR UNKNOWN);'; DECLARE @vars NVARCHAR(1024) = N'@S TINYINT'; EXEC sp_executesql @stmt_known, @vars, 12; EXEC sp_executesql @stmt_unknown, @vars, 12; GO
Das obige Codebeispiel führt eine Abfrage auf die Testtabelle [dbo].[messages] aus. Zunächst wird die Abfrage ohne jegliche Hinweise ausgeführt. Beim zweiten Mal wird die Option OPTIMIZE FOR UNKNOWN als Hinweis verwendet. Die Unterschiede in den Ausführungsplänen sind eindeutig erkennbar:
Bei der Ausführung der Abfrage ohne Hinweis erkennt Microsoft SQL Server (bei leerem Plancache!), wie viele Datensätze zurückgeliefert werden. In diesem Fall verwendet Microsoft SQL Server das Histogramm des Statistikobjekts und kann einen geeigneten Ausführungsplan generieren.
Der zweite Plan – obwohl der identische Wert gesucht wird – verhält sich vollständig anders. An Stelle eines performanten INDEX SEEK entscheidet sich Microsoft SQL Server für einen TABLE SCAN. Der TABLE SCAN ist für die geschätzte Anzahl von Datensätzen ressourcenschonender als ein INDEX SEEK, der 18.167 mal fehlende Informationen aus der Tabelle ermitteln müsste.
Beispiel 2
Von einem ASCENDING KEY Problem spricht man, wenn in einem Index fortlaufende Werte eingetragen werden. Microsoft SQL Server aktualisiert Statistiken nicht sofort. Vielmehr werden Statistiken bis einschließlich Version 2012 bei einer Änderungsrate von 20% aktualisiert. Mit Version 2014 hat sich diese Änderungsrate verändert und aktualisiert in einem dynamischen Verfahren je nach Anzahl der vorhandenen Datensätze bereits früher die Statistiken. Solange Statistiken nicht aktualisiert sind, geht Microsoft SQL Server 2012 immer von einer geschätzten Datenmenge von 1 Datensatz aus. Microsoft SQL Server 2014 schätzt ~30% der Gesamtzahl der Änderungen für die führende Statistikspalte seit der letzten Aktualisierung der Statistiken.
(PS: Die Berechnung ist etwas komplizierter; wird aber für das eigentliche Thema simplifiziert!)
INSERT INTO dbo.messages SELECT message_id, language_id, 30, is_event_logged, text FROM dbo.messages WHERE severity = 11; GO
Zunächst werden weitere 1.870 Datensätze in die Testtabelle eingetragen. Damit ist die Gesamtmenge der Aktualisierungen zu niedrig, als das Statistiken aktualisiert werden! Anschließend wird das vorherige Beispiel erneut ausgeführt.
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE; GO DECLARE @stmt_known NVARCHAR(1024) = N'SELECT * FROM dbo.messages WHERE severity = @S;'; DECLARE @stmt_unknown NVARCHAR(1024) = N'SELECT * FROM dbo.messages WHERE severity = @S OPTION (OPTIMIZE FOR UNKNOWN);'; DECLARE @vars NVARCHAR(1024) = N'@S TINYINT'; EXEC sp_executesql @stmt_known, @vars, 30; EXEC sp_executesql @stmt_unknown, @vars, 30; GO
Die geschätzten Zeilen bei Ausführung der ersten Abfrage liegen deutlich unter den tatsächlichen Zeilen. Es wurden 1.870 neue Datensätze eingetragen. Ca. 28% der neu hinzugefügten Zeilen schätzt Microsoft SQL Server ohne die Verwendung von OPTIMIZE FOR UNKNOWN.
Bei der Verwendung von OPTIMIZE FOR UNKNOWN ist die Schätzung – erneut – deutlich zu hoch. Die Berechnung ist für den Microsoft SQL Server jedoch einfacher. Zu den bereits vorhandenen Datensätzen werden die Änderungen hinzu addiert und anschließend erneut durch 16 geteilt.
Problematik
Die Besonderheit von OPTIMIZE FOR UNKNOWN tritt verstärkt dann auf, wenn die Verteilung der Daten in einem Index sehr ungünstig ist. OPTIMIZE FOR UNKNOWN hilft nur dann, wenn sichergestellt ist, dass die unterschiedlichen Werte eines Index möglichst gleichmäßig verteilt sind. Ansonsten kann sich die “Silver Bullet” gegen Parameter Sniffing und Ascending Key schnell gegen einen wenden, wie das nachfolgende Beispiel verdeutlicht.
DECLARE @stmt_known NVARCHAR(1024) = N'SELECT * FROM dbo.messages WHERE severity = @S ORDER BY language_Id;'; DECLARE @stmt_unknown NVARCHAR(1024) = N'SELECT * FROM dbo.messages WHERE severity = @S ORDER BY language_id OPTION (OPTIMIZE FOR UNKNOWN);'; DECLARE @vars NVARCHAR(1024) = N'@S TINYINT'; EXEC sp_executesql @stmt_known, @vars, 12; EXEC sp_executesql @stmt_unknown, @vars, 12; GO
Sobald ein Stop-Operator mit ins Spiel kommt, muss Microsoft SQL Server Speicher für die Operation reservieren. Für die erste Abfrage verwendet Microsoft SQL Server 1.168 KB Speicher, um die Sortierung der Daten im Speicher durchzuführen. Auf Grund der geringen Datenmenge reicht der angeforderte Speicher aus.
Sobald aber die Option OPTIMIZE FOR UNKNOWN verwendet wird, ist die geschätzte Anzahl von Datensätzen höher und Microsoft SQL Server muss deutlich mehr Speicher für die Operation reservieren, als tatsächlich verwendet wird.
In diesem Fall spricht man von “excessive memory grant”. Für eine einzelne Abfrage ist diese Fehleinschätzung nicht zwingend problematisch. Aber was passiert, wenn 100 Clients diese Abfrage gleichzeitig ausführen?
Auch bei einem Missverhältnis der Schätzung nach oben ergeben sich extreme Probleme bei der Verwendung von OPTIMIZE FOR UNKNOWN. Das nachfolgende Beispiel verwendet einen Indexwert, der ca. 212.000 Datensätze zurück liefert.
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE; GO DECLARE @stmt_known NVARCHAR(1024) = N'SELECT * FROM dbo.messages WHERE severity = @S ORDER BY language_Id;'; DECLARE @stmt_unknown NVARCHAR(1024) = N'SELECT * FROM dbo.messages WHERE severity = @S ORDER BY language_id OPTION (OPTIMIZE FOR UNKNOWN);'; DECLARE @vars NVARCHAR(1024) = N'@S TINYINT'; EXEC sp_executesql @stmt_known, @vars, 16; EXEC sp_executesql @stmt_unknown, @vars, 16; GO
Bei geschätzten 213.931 Datensätzen benötigt Microsoft SQL Server 552.736 KB Arbeitsspeicher, um die Sortierung im Speicher durchzuführen. Außerdem entscheidet sich Microsoft SQL Server – auf Grund der geschätzten Kosten – für die Durchführung der Operation mit einer parallelen Ausführung.
Bei Verwendung von OPTIMIZE FOR UNKNOWN hingegen verschätzt sich der Query Optimizer von Microsoft SQL Server derart, dass sowohl zu wenig Speicher reserviert wird (47.552 KB) als auch von einem parallelen Ausführungsplan auf Grund der geschätzten niedrigen Kosten für die Abfrage abgesehen wird. Besonders problematisch an diesem Beispiel ist, dass auf Grund der Fehleinschätzung für den benötigen Speicher die Sortierung nicht mehr im Speicher ausgeführt werden kann, sondern Microsoft SQL Server die Daten zunächst in die Systemdatenbank TEMPDB schreibt und die Sortierung in TEMPDB durchführt.
Zusammenfassung
Die Option OPTIMIZE FOR UNKNOWN kann helfen, wenn häufig Probleme mit Parameter Sniffing oder mit Ascending Keys auftreten. Eine generelle Verwendung dieser Option ist nicht zielführend; eine Analyse des Datenvolumens und der Datenverteilung sollte auf jeden Fall im Vorfeld beachtet werden. Am wichtigsten ist jedoch, dass diese Option NIEMALS fest im Code einer Applikation implementiert wird. Was dann mit einer Applikation passieren kann, werde ich im nächsten Artikel beschreiben.
Vielen Dank fürs Lesen!
