Parameter Sniffing und Trace Flag 4136

Wer schnüffelt hier an wem und warum könnte das eine Herausforderung für Programmierer sein? Gibt es Abhilfen, oder müssen wir das so hinnehmen?

Um dieses Thema zu verstehen, muss man sich der Arbeitsweise des Query Optimizers nähern, sich mit Statistiken und Plänen beschäftigen und Schätzungen für Trefferquoten akzeptieren. Das hört sich erst mal recht trocken an, hat aber viele Facetten.

(Anschliessend empfehle ich den zweiten Teil dieser Serie: Parameter Sniffing und der Plancache.
Im dritten Teil "Parameter Sniffing und Planhinweislisten" wollen wir lernen, wie man mit Planhinweislisten (Plan Guides) die hinterlegten Pläne im Plancache beeinflussen kann.)

Nähern wir uns dem Thema mal mit einem Hilferuf:

Heute ist die Prozedur sooo langsam!

... aber gestern war sie noch schnell!

Wo liegt denn eine mögliche Ursache, wenn die Ausführungszeit eines Statements oder einer Prozedur nach Tageszeit variiert? Das folgende Beispiel soll die Einflussgrößen verdeutlichen und Möglichkeiten zur Abhilfe aufzeigen.

Mein Beispiel habe ich in Anlehnung an diesen blog Eintrag I Smell a Parameter! geschrieben. Ich habe es für meine Bedürfnisse angepasst und mit SQL Server 2012 getestet. Allerdings sollte es so bereits ab Version SQL Server 2005 laufen. Man wird sicherlich diesen Aufwand nicht für alle Statements betreiben müssen, sondern nur für diejenigen, die durch stark schwankende Performance auffallen. In der Regel dürften nur solche Tabellen betroffen sein, in denen die Verteilung der Werte stark unterschiedlich ist.

Teil 1: Wir erzeugen Beispieldaten

Vorab müssen wir natürlich erst mal eine Tabelle erstellen. Wir machen dies in der tempdb, damit wir keinem in die Quere kommen. ACHTUNG: Das Skript bitte nicht auf einem produktiven Server ausführen, da wir zwischendurch mehrfach den Cache leeren, in dem die Zugriffspläne für alle Statements der Instanz liegen.

Wir erzeugen eine Tabelle mit 2000 Zeilen. 1000 Zeilen haben die Werte von 1 bis 1000 (ohne Duplikate). Danach kommen noch 1000 Zeilen mit dem Wert 5000.

CREATE TABLE t(col1 INT);
DECLARE @i INT;
SET @i = 0;

WHILE @i < 1000
BEGIN
   INSERT INTO
t(col1) VALUES (@i);
  
SET @i = @i + 1;
END;

SET @i = 0;
WHILE @i < 1000
BEGIN
   INSERT INTO
t(col1) VALUES (5000);
  
SET @i = @i + 1;
END;

Wir legen eine fullscan statistic für die Spalte der Tabelle an.

CREATE STATISTICS t_col1 ON t(col1) WITH fullscan;

Man beachte die Selektivität für die Spalte (All density) die sich wie folgt berechnet 1/1001 (gerundet 9.99001E-4). Es gibt 1001 distinkte Werte in der Tabelle.

DBCC show_statistics ('dbo.t','t_col1');

Teil 2: Parameter Sniffing bei einfachen Statements

Wir rufen jetzt das Statement auf, ohne einen Wert für den Parameter zu setzen. Die "geschätzte Anzahl von Zeilen" beläuft sich auf 1,998! Dieser Wert ergibt sich aus der Rechnung: 2000 Zeilen * 1/1001 (Rows * All density). Das Ergebnis ist also ungefähr 2 Zeilen, was bei den meisten Fällen ziemlich nahe an der Realität (1) ist. Nur bei einem Fall (5000) ist es sehr weit von der Realität (1000) entfernt.

DECLARE @p INT;
SELECT * FROM t WHERE col1 = @p;

Das gleiche nochmal, aber jetzt mit einem Wert. Der Wert 5000 kommt 1000 mal in der Tabelle vor, aber die Schätzung bleibt bei 1,998. Warum? Nun, wir kompilieren den Stapel bevor wir ihn ausführen. Deshalb sieht der Optimizer den Wert nicht und das Ergebnis ist das gleiche wie beim Beispiel davor, wo der Parameter noch NULL beinhaltet.

DBCC freeproccache;
DECLARE @p INT;
SET @p = 5000;
SELECT * FROM t WHERE col1 = @p;

Option RECOMPILE

Jetzt versuchen wir mit der Option RECOMPILE den Optimizer zu einem anderen Vorgehen zu überreden.

Aus der Online-Doku:

  • RECOMPILE
    Weist SQL Server Database Engine (Datenbankmodul) an, den für die Abfrage generierten Abfrageplan nach der Ausführung zu verwerfen. Dadurch wird der Abfrageoptimierer gezwungen, erneut einen Abfrageplan zu kompilieren, wenn dieselbe Abfrage das nächste Mal ausgeführt wird. Ohne das Angeben von RECOMPILE werden Abfragepläne von Database Engine (Datenbankmodul) zwischengespeichert und wiederverwendet. Beim Kompilieren von Abfrageplänen verwendet der RECOMPILE-Abfragehinweis die aktuellen Werte von lokalen Variablen in der Abfrage und, falls sich die Abfrage innerhalb einer gespeicherten Prozedur befindet, die an Parameter übergebenen aktuellen Werte.

Unter SQL Server 2012 erhält man mit RECOMPILE jetzt den exakten Wert (0 Zeilen) für den mit NULL initialisierten Parameter.

DBCC freeproccache;
DECLARE @p INT;
SELECT * FROM t WHERE col1 = @p
OPTION(RECOMPILE);

Jetzt schauen wir uns den Plan an, wenn wir den Wert vorab setzen. Wir erwarten genau 1 Zeile. Nach dem RECOMPILE wird auch die "geschätzte Anzahl von Zeilen" korrekt mit 1 angegeben. Weiterhin sehen wir im Ausführungsplan, dass der Parameter @p durch den Wert 1 ersetzt wurde.

DECLARE @p INT;
SET @p = 1;
SELECT * FROM t WHERE col1 = @p
OPTION(RECOMPILE);

Option OPTIMIZE FOR

Ein anderer (und besserer) Workaround ist die Verwendung des OPTIMIZE FOR Query-Hint. Er umgeht das Rekompilieren und bringt die korrekte Anzahl von 1 Zeile in der Schätzung. Hier kommt die Schätzung jetzt auf 1 Zeile, anstelle der 1,998 Zeilen, wenn man die Option weglässt. Das Prädikat der Abfrage bleibt unverändert bei @p.

Aus der Online-Doku:

  • OPTIMIZE FOR ( @variable_name { UNKNOWN | = literal_constant } [ , ...n ] )
    Weist den Abfrageoptimierer an, einen bestimmten Wert für eine lokale Variable zu verwenden, wenn die Abfrage kompiliert und optimiert wird. Dieser Wert wird nur während der Abfrageoptimierung verwendet, nicht während der Abfrageausführung.
DECLARE @p INT;
SET @p = 1;
SELECT * FROM t WHERE col1 = @p
OPTION (OPTIMIZE FOR (@p = 1));

Hier kommen jetzt geschätzt 1000 Zeilen raus, wie wir es auch erwartet haben:

SET @p = 5000;
SELECT * FROM t WHERE col1 = @p
OPTION (OPTIMIZE FOR (@p = 5000));

Teil 3: Sniffing bei Stored Procedures

Als nächsten Workaround könnte man für das obige Statement auch eine Stored Procedure verwenden. Andere Anwender kommen direkt mit Schwierigkeiten bei Prozeduren an, ohne vorher diese Auffälligkeiten an einzelnen Statements entdeckt zu haben.
Die Verwendung von Stored Procedures ermöglicht dem Optimizer den Parameter zu "sehen" und den Wert zum Zeitpunkt der Kompilierung zu bestimmen

Legen wir also eine einfache Prozedur an, die wieder genau das gleiche Statement beinhaltet.

CREATE PROCEDURE foo (@p INT)
AS
RETURN SELECT
* FROM t WHERE col1 = @p;
GO

Wir schauen uns den geschätzten Ausführungsplan an, wenn wir die Prozedur mit dem Parameter @p=1 aufrufen. Die Schätzung beläuft sich wie erhofft auf 1 Zeile anstelle der 1,998 Zeilen.

EXECUTE foo @p=1;

Wie kann man jetzt erkennen, dass Parameter-Sniffing verwendet wurde? Dies ist im XML-Showplan sichtbar, wobei dieser ausserhalb des Management Studios mit einem XML-Viewer geöffnet werden sollte.

SET showplan_xml ON;
GO
EXECUTE foo @p=1;

Speichere den XML-Plan als XML-Datei ab (ohne Header) und öffne sie z. B. mit dem Internet-Explorer. Suche den Knoten mit der Parameter-Liste:
<ParameterList>   <ColumnReference Column="@p" ParameterCompiledValue="(1)"/> </ParameterList>

Was kommt denn jetzt für ein Plan, wenn der Parameter 5000 verwendet wird?
Hier kommen ja 1000 Zeilen als Ergebnis, aber der geschätzte Ausführungsplan ist der gleiche wie bei 1!

EXECUTE foo @p=5000;

Die Ursache: Der Plan für die Prozedur befindet sich im Cache und muss nicht neu erstellt werden.
Zur Erzeugung wurde der Wert 1 verwendet, was bei einem Aufruf mit 5000 nicht die richtige Schätzung ergibt und in der Folge zu einem nicht optimalen Plan führen kann.

Wenn jetzt im Laufe des Tages dieser Plan aus dem Cache verdrängt wird, jemand die Instanz neustartet, oder den Cache über DBCC leert, dann entscheidet der nächste Aufruf der Prozedur über den abgespeicherten Plan. Und genau hier liegt die Ursache in der "schwankenden" Performance.

Wir leeren den Cache und starten die Prozedur initial mit dem Parameter @p=5000.

DBCC freeproccache;
GO
EXECUTE foo @p=5000;

Der Ausführungsplan ist für diesen Aufruf optimal, wird aber auch für alle Folgeaufrufe verwendet. Von den Anwendern wird dies wie folgt wahrgenommen: Manchmal geht es schnell und am nächsten Tag dann nur noch langsam. Dass der Sonderfall mit dem Parameter 5000 an diesem Tag vielleicht schneller läuft, wird nicht wahrgenommen. Vielleicht auch, weil die Laufzeit für 1000 Zeilen sowieso schon mal höher ist.

Workaround für Prozeduren

Nächster Workaround: Die Prozedur mal etwas abändern um das Sniffing zu unterbinden. Durch die Kapselung in einer lokalen Variablen verliert der Optimizer die Spur zum übergebenen Wert. Die Schätzung ist jetzt immer gleich, aber nicht korrekt.
StatementEstRows="1.998"

ALTER PROCEDURE foo (@p INT)
AS
DECLARE
@My_P INT;
SET @My_P = @p;
RETURN SELECT * FROM t WHERE col1 = @My_P;
GO

Jetzt sind die Pläne immer gleich, ganz egal wie der Aufruf erfolgt!

Weitere Optimierung für Prozeduren

Wer mit diesem nicht ganz optimalen Plan nicht leben möchte, kann versuchen dies weiter zu optimieren.

Man kann hier natürlich auch wieder mit der RECOMPILE oder OPTIMIZE FOR Option arbeiten. Alternativ legt man für besonders auffällige Parameterwerte gesonderte Prozeduren an um auch dort optimale Pläne zu erhalten.

Teil 4: Trace Flag 4136

Nun kommt das Trace Flag 4136 ins Spiel, welches seit einiger Zeit verfügbar ist. Wie alle Trace Flags wirkt es sich auf die gesamte Instanz aus und dort liegt auch das Problem.

In diesem blog Eintrag wird das Trace-Flag beschrieben und auch die kumulativen Updates, mit denen es eingeführt wurde.

Conor Cunningham (Microsoft) hat dazu den SQL Server MVPs geschrieben: It stops sniffing parameter values for the whole instance. I don’t think you should use this unless absolutely necessary, as it is far too broad for most customers.

Wie bei jedem Trace-Flag gilt es auch hier die Auswirkungen gründlich zu testen. Instanzen mit vielen Datenbanken und unterschiedlichen Anwendungen werden wohl eher damit in Schwierigkeiten laufen, als dedizierte Instanzen. Der Autor des oben erwähnten blog-Artikels beschäftigt sich mit Dynamics AX, wo es evtl. hilfreich sein könnte.

Von Torsten Schüßler gibt es eine schöne Anleitung zum Setzen der Trace Flags, so dass hier auf eine detaillierte Beschreibung verzichte.

Microsoft listet alle dokumentierten Ablaufverfolgungsflags in diesem Artikel auf. Das Trace Flag 4136 gehört nicht dazu.

Auswirkungen auf Prozeduren

Auf mein Beispiel mit der ersten Prozedur (ohne lokale Variable) angewendet, bedeutet das Setzen von Trace Flag 4136, dass jetzt immer der Wert 1,998 für die geschätzte Anzahl von Zeilen ermittelt wird. Hier wird also nicht mehr das Histogamm der Werte, sondern der Wert Rows * All density verwendet. Das entspricht also dem Verhalten, was nach der Kapselung des Statements mit einer lokalen Variablen erreicht wird.

Falls man also keinen Einfluss auf die Stored Procedures hat, oder diese nicht in großem Umfang ändern möchte, kann man über das Trace Flag unter Umständen eine Verbesserung der Ausführungspläne erreichen.

Wer aber ganz gezielt einzelne Prozeduren ausmachen kann, ist sicherlich besser beraten die Kapselung oder andere Optimierungen zu probieren.

Auswirkungen auf Statements

Führe ich meine Statement mit sp_executesql aus, dann hat das Trace Flag auch hierauf einen Einfluss.

DBCC freeproccache;
GO
DECLARE @Befehl         NVARCHAR(1000),
                
@ParmDefinition NVARCHAR(100),
                
@ParmValue      INT;    

SET @Befehl = 'select * from t where col1 = @p';
SET @ParmDefinition = N'@p int';
SET @ParmValue = 5000;

EXECUTE sp_executesql @Befehl, @ParmDefinition, @p = @ParmValue;

Auch hier erreiche ich mit dem Trace Flag die Verwendung des Wertes für "All density" anstelle des Histogramms.