Parameter Sniffing und der Plancache

Heute wollen wir mal einen etwas genaueren Blick auf den Plancache werfen und noch einmal betrachten, wie dort abgelegte Pläne die Ausführungszeiten von Statements beeinflussen.

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 nur eine Möglichkeit kennen gelernt den zur Kompilierung verwendeten Parameter anzeigen zu lassen. Die weiteren Auswirkungen auf Pläne haben wir auch noch nicht betrachtet.

Im dritten Teil "Parameter Sniffing und Planhinweislisten" wollen wir lernen, wie man mit Planhinweislisten (Plan Guides) die hinterlegten Pläne im Plancache beeinflussen kann.

Pläne werden im Plancache des SQL Server abgelegt und nach Möglichkeit wiederverwendet. Die Hintergründe lassen sich im Artikel Zwischenspeichern und Wiederverwenden von Ausführungsplänen nachlesen. Die Namensgebung Prozedurcache wurde mittlerweile durch die Bezeichnung Plancache abgelöst.

Wie man die Verwendung des Plancache für Statements optimieren kann, die in der Regel nur einmalig aufgerufen werden, habe ich im Artikel Optimieren für Ad-hoc-Arbeitsauslastungen beschrieben.

Wir erzeugen Beispieldaten

Damit der Plancache wirkungsvoll ins Spiel kommt, darf man nicht mit solchen einfachen SQLs arbeiten, wie im ersten Beispiel, sondern wir werden hier jetzt zwei Tabellen haben und diese per Join verknüpfen.

Alle Beispiele und Analysen sollten natürlich nicht auf einem produktiven System nachgestellt werden, da insbesondere die Auswertung des Plancache recht umfangreiche Ressourcen benötigen kann.

Wir benötigen 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).

CREATE TABLE t(col1 INT, pk_t INT IDENTITY NOT NULL);
ALTER TABLE t ADD CONSTRAINT XPKt PRIMARY KEY  (pk_t);
CREATE INDEX XIE1t ON t(col1);

CREATE TABLE u(pk_t INT NOT NULL, id INT NOT NULL, u_value VARCHAR(5));
ALTER TABLE u ADD CONSTRAINT XPKu PRIMARY KEY (pk_t, id);
ALTER TABLE u ADD CONSTRAINT FK_t FOREIGN KEY (pk_t) REFERENCES t(pk_t);


DECLARE @i INT;
DECLARE @PK INT;
SET @i = 0;

WHILE @i < 1000
BEGIN
   INSERT INTO
t(col1) VALUES (@i);
  
SET @PK = SCOPE_IDENTITY();
  
INSERT INTO u(pk_t, id, u_value) VALUES (@PK, 1, CAST(@i AS VARCHAR(5)));
  
INSERT INTO u(pk_t, id, u_value) VALUES (@PK, 2, CAST(@i AS VARCHAR(5)));
  
SET @i = @i + 1;
END;

SET @i = 0;
WHILE @i < 1000
BEGIN
   INSERT INTO
t(col1) VALUES (5000);
  
SET @PK = SCOPE_IDENTITY();
  
INSERT INTO u(pk_t, id, u_value) VALUES (@PK, 1, CAST(@i AS VARCHAR(5)));
  
INSERT INTO u(pk_t, id, u_value) VALUES (@PK, 2, CAST(@i AS VARCHAR(5)));
  
SET @i = @i + 1;
END;

Verwende eine Stored Procedure

Die Verwendung einer Prozedur ermöglicht dem Optimizer den Parameter zu "sehen" und den Wert zum Zeitpunkt der Kompilierung zu bestimmen.

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 XIE1t11
50 % Clustered Index Seek XPKu22
0 % Nested Loops22

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 XIE1t11000
50 % Clustered Index Seek XPKu22000
0 % Nested Loops22000

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. Aber dazu später mehr.

Die Werte aus dem Plancache anzeigen

Der Wert des Parameters, der für die Kompilierung des Plans verwendet wurde, läßt sich u. a. auf diesem Weg anzeigen:

  1. Kontextmenü des Select-Statements: Ausführungsplan-XML-Datei anzeigen
  2. Kontextmenü des Select-Statements: Eigenschaften
  3. Der direkte Blick in den Plancache (s. u.)

1. Ausführungsplan-XML-Datei anzeigen

Hierüber erhält man direkt im Management-Studio die XML-Darstellung des Zugriffsplans anzeigen. Hierdrin sucht man nach der Parameterliste und findet dort die beiden Werte für "kompilierter Parameter" (ParameterCompiledValue) und den Laufzeitwert (ParameterRuntimeValue).

Dieser Plan wurde also mit dem Wert 1 erzeugt, aber mit dem Wert 5000 wurde die Prozedur dann aufgerufen.

2. Eigenschaften des Select-Operators

Nach der Auswahl des Select-Operators lässt sich dann über das Kontextmenü im Eigenschaftsfenster die Parameterliste anzeigen.

Es werden sowohl der kompilierte Wert, als auch der Laufzeitwert angezeigt. Also erfolgte auch hier der Aufruf mit dem Wert 5000, wohingegen der Plan mit dem Wert 1 kompiliert wurde.

Einen anderen Plan ablegen

Jetzt machen wir es einmal anders herum. Es soll der Plan also mit dem Wert 5000 kompiliert werden. Damit nur der alte Plan aus dem Cache verschwindet, wird die Prozedur gedropped und neu angelegt.

DROP PROCEDURE foo;
GO
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
;

Wir rufen jetzt wieder die Prozedur auf, aber diesmal zuerst mit dem Parameter 5000, damit der Plan dafür im Cache gehalten wird.

EXECUTE foo @p=5000;

Die Schätzung beläuft sich jetzt auf 2000 Zeilen für das gesamte Statement. Wir sehen einen etwas anderen Ausführungsplan mit den folgenden Komponenten:

Aktion / Zeilen geschätzt tatsächlich
14 % Index Seek XIE1t10001000
42 % Clustered Index Scan XPKu40004000
44 % Merge Join20002000

Table 'u'. Scan count 1, logical reads 14, 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 viel besser als in der ersten Variante, die mit @p=1 kompiliert wurde. 1 Scan anstelle von 1000 und 14 logical reads auf u anstelle von 2008!

Nun verwenden wir den Wert 1 als Parameter, obwohl der Plan ja für 5000 optimiert wurde.

EXECUTE foo @p=1;

Der Ausführungsplan bleibt der gleiche, aber die Anzahl der Zeilen variiert und liegt teilweise erheblich neben der Realität.

Aktion / Zeilen geschätzt tatsächlich
14 % Index Seek XIE1t10001
42 % Clustered Index Scan XPKu40005
44 % Merge Join20002

Die Werte für das IO sind gegenüber der optimalen Variante für diesen Parameter schlechter geworden, bleiben aber im Rahmen des erträglichen.

Table 'u'. Scan count 1, logical reads 3, 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 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Der direkte Blick in den Plancache

Auch ohne ein Statement auszuführen, können wir den direkten Blick in den Plancache werfen. Wenn wir den Namen der Prozedur kennen, können wir die dort hinterlegten Parameter einfach abrufen.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
GO
WITH XMLNAMESPACES (DEFAULT
'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
,
PlanParameters
AS (
SELECT ph.plan_handle, qp.query_plan, qp.dbid, qp.objectid
FROM sys.dm_exec_cached_plans ph
OUTER APPLY sys.dm_exec_query_plan(ph.plan_handle) qp
WHERE qp.query_plan.exist('//ParameterList')=1
AND OBJECT_NAME(qp.objectid, qp.dbid) = 'foo'
)
SELECT
DB_NAME(pp.dbid) AS DatabaseName
,OBJECT_NAME(pp.objectid, pp.dbid) AS ObjectName
,n2.value('(@Column)[1]','sysname') AS ParameterName
,n2.value('(@ParameterCompiledValue)[1]','varchar(max)')
AS ParameterValue
FROM PlanParameters pp
CROSS APPLY query_plan.nodes('//ParameterList') AS q1(n1)
CROSS
APPLY n1.nodes('ColumnReference') AS q2(n2)
;

Das Ergebnis ist der Name der Prozedur und die Parameter-Werte, die zur Kompilierung verwendet wurden.

  Parameter_Sniffing_Beispiel_Erweitert.sql