Im dritten Teil wollen wir eine weitere Methode kennen lernen, wie die hinterlegten Pläne im Plancache beeinflusst werden können. Dies ist insbesondere dann interessant, wenn wir es mit Prozeduren oder Statements zu tun bekommen, die wir nicht einfach mit Optimierungshinweisen verändern können.

Im ersten Teil dieser Mini-Serie mit dem Titel "Parameter Sniffing und Trace Flag 4136" ging es darum, überhaupt einmal zu definieren, was Parameter Sniffing ist und welche Auswege sich hierfür bieten. Wir haben dort verschiedene Möglichkeiten zur Optimierung kennen gelernt.

Im zweiten Teil mit dem Titel Parameter Sniffing und der Plancache haben wir einen genaueren Blick in den Plancache geworfen und gesehen, dass für die selbe Prozedur zwei sehr unterschiedliche Pläne dort abgelegt sein können.

Hier geht es nun um Plan Guide, oder in der Übersetzung um Planhinweislisten. Mit einem Plan Guide wollen wir den Optimizer in die gewünschte Richtung führen, damit er nicht nur auf seine Schätzung anhand des ersten Aufrufs angewiesen ist.

Beispieldaten und Prozedur

Wer die beiden ersten Teile noch nicht gelesen hat, sollte sich zumindest kurz die dort verwendeten Beispieldaten anschauen. Hier werden wieder die beiden Tabellen und die Prozedur aus dem zweiten Teil verwendet.

Wir haben also zwei Tabellen mit Indizes und einigen Daten. 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. Die zweite Tabelle hat für jeden Satz aus der ersten Tabelle 2 Sätze (ID=1,2).

Die Verwendung einer Prozedur ermöglicht dem Optimizer den Parameter zu "sehen" und den Wert zum Zeitpunkt der Kompilierung zu bestimmen. Hier noch einmal der Code zur Erinnerung:

CREATE PROCEDURE foo (@p INT)
AS
SELECT t.pk_t, t.pk_t, u.u_value
FROM t
INNER JOIN u
   ON t.pk_t = u.pk_t
WHERE t.col1 = @p
;

Erster Aufruf der Prozedur

Wir rufen die Prozedur einmal mit einem Parameter auf. Danach schauen wir uns den geschätzten Ausführungsplan für die Ausführungen an.

EXECUTE foo @p=1;

Die Schätzung für das Ergebnis beläuft sich auf 2 Zeilen. Im Ausführungsplan sehen wir folgende Details:

Aktion / Zeilen	geschätzt	tatsächlich
50 % Index Seek XIE1t	1	1
50 % Clustered Index Seek XPKu	2	2
0 % Nested Loops	2	2

Die Informationen über das IO haben wir uns mit diesem Statement zugänglich gemacht:

SET STATISTICS IO ON;

Table 'u'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 't'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Jetzt rufen wir diese Stored Procedure erneut auf, diesmal aber mit einem anderen Parameter.

EXECUTE foo @p=5000;

Der generierte Ausführungsplan ist identisch, nur die tatsächlichen Zeilen und der Aufwand für das IO variieren.

Aktion / Zeilen	geschätzt	tatsächlich
50 % Index Seek XIE1t	1	1000
50 % Clustered Index Seek XPKu	2	2000
0 % Nested Loops	2	2000

Table 'u'. Scan count 1000, logical reads 2008, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 't'. Scan count 1, logical reads 5, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Das ist nicht der optimale Plan mit Scan count 1000 und 2008 logical reads auf u. Aus dem zweiten Teil wissen wir ja, dass der Plan mit dem Parameter @p=5000 erstellt wesentlich optimaler ist. Auch für die anderen Aufrufe mit z. B. @p=1 wird der Plan nicht so schlecht, als das wir nicht damit leben könnten.

Das Ziel

Unser Ziel ist es also, den Optimizer ohne Änderung der Prozedur davon zu überzeugen, dass immer der Plan verwendet werden soll, der für den Parameter @p=5000 optimiert wurde.

Was wir nicht tun wollen, ist eine Optimierung in die Prozedur einbauen, wie es im ersten Teil beschrieben wurde. Auch die Verwendung des dort beschriebenen Trace-Flags möchten wir nicht angehen, da dieses ja die gesamte Instanz beeinflussen würde.

Weiterhin wollen wir nicht davon abhängig sein, dass er erste Aufruf der Prozedur mit dem Parameter @p=5000 erfolgen muss, wenn der Plan noch nicht im Plancache ist, oder bereits wieder von dort verdrängt wurde.

Der Weg

Im SQL Server gibt es die Möglichkeit einen Plan Guide anzulegen, wobei für diesen Hinweis drei Bestandteile zur Identifizierung des Statements wichtig sind.

1. Das Statement selber
2. Der Typ des Objekts (z. B. Prozeduren, Funktionen, Trigger oder eigenständige SQL-Anweisungen)
3. Der Name des Objektes, wenn es keine eigenständige SQL-Anweisung ist

Jetzt verwenden wir den Plan Guide, damit wir die Abfrage innerhalb der Prozedur mit dem Parameter @p=5000 optimieren. Man beachte hierbei die Auswirkungen von Planhinweislisten auf den Plancache in der Online-Doku zu sp_create_plan_guide :

• Wenn Sie eine Planhinweisliste für ein Modul erstellen, wird der Abfrageplan für dieses Modul aus dem Plancache entfernt.
• SQL Server ignoriert die Zeichen Wagenrücklauf und Zeilenvorschub sowie Leerzeichen in der Abfrage
• Bei der Feststellung der Übereinstimmung wird nach Groß- und Kleinschreibung sowie nach Akzenten unterschieden (selbst wenn die Sortierung der Datenbank die Groß-/Kleinschreibung nicht berücksichtigt), mit Ausnahme von Schlüsselwörtern, bei denen keine Unterscheidung nach Groß-/Kleinschreibung stattfindet.
• Bei der Feststellung der Übereinstimmung wird nicht nach verkürzten Formen von Schlüsselwörtern unterschieden. So werden beispielsweise die Schlüsselwörter EXECUTE, EXEC und execute als gleichwertig angesehen.

In diesem Beispiel unterscheidet sich das SQL zwar durch die Einrückung (per Tabs oder Leerzeichen) im Editor, wird aber trotzdem für die Prozedur erkannt.

EXEC sys.sp_create_plan_guide @name = 'InsideSql', -- sysname
    @stmt = N'Select t.pk_t, t.pk_t, u.u_value
                  From t
                  Inner Join u
                  on t.pk_t = u.pk_t
                  where t.col1 = @p', -- nvarchar(max)
    @type = N'Object', -- nvarchar(60)
    @module_or_batch = N'dbo.foo', -- nvarchar(max)
    @params = NULL, -- nvarchar(max)
    @hints = N'OPTION(OPTIMIZE FOR(@p = 5000))' -- nvarchar(max)
   ;


Ziel erreicht?

Nachdem wir jetzt den Plan Guide erstellt haben, sollte jeder Aufruf auch diesen für @p=5000 optimierten Plan verwenden. Wir können dies wieder im Ausführungsplan kontrollieren, denn hier wird jetzt neben den verwendeten Parametern auch der verwendete Plan Guide angezeigt.

Wir rufen jetzt wieder die Prozedur auf, wieder mit dem Parameter @p=1. Dann sehen wir bereits einen anderen Ausführungsplan als beim ersten Versuch oben.

EXECUTE foo @p=1;

Wenn wir uns die Eigenschaften der Select-Komponente anzeigen lassen, sehen wir jetzt auch den Plan Guide:

Es wird also ein Plan Guide mit dem Namen "InsideSql" verwendet und der kompilierte Wert für den Parameter war @p=5000. Auch in der XML-Ausgabe des Ausführungsplans sind diese Informationen zu finden.

<StmtSimple ...         PlanGuideDB="tempdb" PlanGuideName="InsideSql" ...

In der Zukunft wird diese Prozedur also immer einen Plan verwenden, der für den Langläufer mit @p=5000 optimiert wurde und für die schnellen Aufrufe mit z. B. @p=1 fast identische Antwortzeiten liefert.

Plan Guides findet man z. B. im Management Studio bei der Datenbank im Zweig der Programmierbarkeit:

Man kann sich diese aber auch mit T-SQL anschauen:

SELECT name AS plan_guide_name,
    create_date,
    query_text,
    scope_type_desc,
    OBJECT_NAME(scope_object_id) AS scope_object_name,
    scope_batch,
    parameters,
    hints,
    is_disabled
FROM sys.plan_guides
WHERE name = N'InsideSql';

Die weiteren Auswirkungen

Da es sich bei Planhinweislisten also um Objekte in der Datenbank handelt, können diese mit gesichert werden und bleiben auch nach einer Wiederherstellung der Datenbank erhalten. Bei einem Server Upgrade sollte man genau prüfen, ob diese noch notwendig sind.

Plan Guides für Prozeduren sind Objekte, die von anderen Objekten (den Prozeduren) abhängig sind. Man kann nicht einfach die Prozedur droppen, wenn es noch einen Plan Guide dafür gibt. Der Versuch:

DROP PROCEDURE dbo.foo;

schlägt fehl mit der Meldung:

Meldung 10513, Ebene 16, Status 1, Zeile 1 Löschen ist für das Prozedur-Objekt 'dbo.foo' nicht möglich, weil von der Planhinweisliste 'InsideSql' darauf verwiesen wird. Verwenden Sie sp_control_plan_guide, um die Planhinweisliste erst zu löschen. Zeichnen sie die Definition der Planhinweisliste für die spätere Verwendung auf.

In der Online-Doku steht: Der Versuch, eine Funktion, gespeicherte Prozedur oder einen DML-Trigger zu löschen oder zu ändern, auf die bzw. den eine aktivierte oder deaktivierte Planhinweisliste verweist, führt zu einem Fehler. Auch der Versuch, eine Tabelle mit einem Trigger zu löschen, auf den eine Planhinweisliste verweist, führt zu einem Fehler.

Man kann diese Plan Guides auch deaktivieren, aktivieren und löschen. Die Statements dazu sind simpel:

Deaktiviere den Plan Guide

Damit verschwindet auch der Plan aus dem Cache. Der nächste Aufruf entscheidet wieder über den Plan im Plancache.

EXEC sp_control_plan_guide N'DISABLE', N'InsideSql';

Aktiviere den Plan Guide wieder

Damit verschwindet auch der zwischenzeitlich ohne Optimierung erstellte Plan aus dem Cache. Der nächste Aufruf wird mit dem Plan Guide durchgeführt und der daraus resultierende Plan im Plancache abgelegt.

EXEC sp_control_plan_guide N'ENABLE', N'InsideSql';

Lösche den Plan Guide

Damit verschwindet auch der Plan aus dem Cache. Der nächste Aufruf entscheidet wieder über den Plan im Plancache. Die Prozedur kann jetzt auch wieder gedropped werden.

EXEC sp_control_plan_guide N'DROP', N'InsideSql';

Schlussbemerkung

Das Feature "Plan Guide" ist übrigens nicht in der Express Edition enthalten. Für alle anderen Editionen ist es ein Feature, dessen Einsatz genauestens geplant und dokumentiert werden sollte. Hierzu ist ein tiefgreifendes Verständnis der Daten und der Applikationen notwendig.