Verwenden der Option OPTION (RECOMPILE) für eine Anweisung

Ich habe diese Serie mit dem Beitrag mit dem Titel: Kleine Bobby-Tabellen, SQL Injection und EXECUTE AS. Dann bin ich dazu übergegangen, einige der Unterschiede mit dem Beitrag: EXEC und sp_executesql – wie unterscheiden sie sich?

Heute möchte ich ein paar der Kommentare ansprechen sowie mit ein paar Tipps und Tricks bei der Verwendung dieser Befehle fortfahren.

Zunächst einmal – hätten wir die Performance der sp_executesql-Anweisung verbessern können?

Ja…

Wenn wir wissen, dass eine Anweisung eine variierende Datenmenge zurückgibt (basierend auf den übergebenen Parametern), dann können wir die SQL Server 2005-Funktion WITH RECOMPILE verwenden, um SQL Server mitzuteilen, dass für die auszuführende Anweisung ein eigener Plan erstellt werden soll und dass frühere Pläne (falls vorhanden) die Anweisung nicht wiederverwenden sollen. Außerdem wird SQL Server mitgeteilt, dass dieser spezielle Plan ein „Single-Use-Plan“ ist, der nicht für nachfolgende Benutzer wiederverwendet werden soll. Um die Kombination all dieser Dinge zu sehen – werde ich einige der DMVs verwenden, die den Plan-Cache und die Planauslastung verfolgen.

DBCC FREEPROCCACHE
GO

SELECT st.text, qs.EXECUTION_COUNT -, qs.*, cp.*
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS cp
WHERE st.text like ‚%FROM dbo.member%‘
GO

Im Moment liefert diese Abfrage 0 Zeilen.

Ich führe Folgendes aus und prüfe dann den Plan-Cache erneut:

DECLARE @ExecStr nvarchar(4000)
SELECT @ExecStr = ‚SELECT * FROM dbo.member WHERE lastname LIKE @lastname‘
EXEC sp_executesql @ExecStr, N’@lastname varchar(15)‘, ‚Tripp‘
GO

Nun haben wir eine Zeile für unseren parametrisierten Abfrageplan:

text EXECUTION_COUNT(@lastname varchar(15))SELECT * FROM dbo.member WHERE lastname LIKE @lastname 1

So, was zeigt uns das… es zeigt uns, dass es einen Plan im Cache für diese Anweisung gibt. Und wenn wir daran interessiert sind, den Plan zu sehen, können wir das auskommentierte cp.* in der Abfrage oben entfernen, um die Spalte cp.query_plan zu erhalten. Klicken Sie auf einen XML-Showplan und SSMS wechselt DIREKT in ein grafisches Abfrageplan-Fenster (ab 2008):

Und wieder sehen wir den optimalen Plan (den Index zu verwenden und eine Lesezeichensuche durchzuführen), da diese Abfrage sehr selektiv ist (nur 1 Zeile).

Wir führen genau die gleiche Anweisung noch einmal aus – mit dem Wert von Anderson, nur um an den Punkt zu kommen, an dem wir letzte Woche waren:

DECLARE @ExecStr nvarchar(4000)
SELECT @ExecStr = ‚SELECT * FROM dbo.member WHERE lastname LIKE @lastname‘
EXEC sp_executesql @ExecStr, N’@lastname varchar(15)‘, ‚Anderson‘
GO

Und wir sehen, dass es den EXAKT gleichen Plan verwendet (wenn wir uns den Showplan ansehen). Tatsächlich können wir das auch sehen, wenn wir unseren Plan-Cache überprüfen:

text EXECUTION_COUNT(@lastname varchar(15))SELECT * FROM dbo.member WHERE lastname LIKE @lastname 2

Und während wir wissen, dass dieser Plan (den Index zu verwenden) gut für den hochselektiven Wert von ‚Tripp‘ ist, ist er nicht gut für den Wert von Anderson, da es viele Zeilen gibt, die übereinstimmen. Wenn wir dies ahnen und/oder bei der Ausführung (vom Client) wissen würden, könnten wir mit OPTION (RECOMPILE) SQL Server zwingen, einen neuen Plan zu holen:

DECLARE @ExecStr nvarchar(4000)
SELECT @ExecStr = ‚SELECT * FROM dbo.member WHERE lastname LIKE @lastname OPTION (RECOMPILE)‘
EXEC sp_executesql @ExecStr, N’@lastname varchar(15)‘, ‚Anderson‘
go

Der Abfrageplan als Ergebnis dieser Ausführung ist:

Und das ist der optimalere Plan angesichts dieses Parameters. An diesem Punkt sind die Fragen (IMO):

1. Wäre jemand wirklich in der Lage, programmatisch einzuschätzen, dass ein vom Client übermittelter Parameter „atypisch“ ist und/oder dass er eine Neukompilierung rechtfertigt? Bei einigen Parametern – z. B. bei denen mit einem Platzhalter – kann ich das wohl mit Ja beantworten. Aber wenn es sich nur um zwei verschiedene Werte für eine einzelne Spalte handelt, wäre das eine statistische Schätzung der Daten.
2. Sollte diese bestimmte Anweisung immer ausgeführt werden, um neu zu kompilieren und niemals einen Plan zu speichern?
3. Was hat SQL Server mit dem Plan selbst gemacht?

Ich fange mit der Antwort #3 an, da diese einfach zu beantworten ist. Ich verwende dieselbe Anweisung und frage erneut den Plan-Cache ab:

text EXECUTION_COUNT(@lastname varchar(15))SELECT * FROM dbo.member WHERE lastname LIKE @lastname 2

Auch wenn wir diese Anweisung zum dritten Mal ausgeführt haben, wurde diese letzte Ausführung NICHT in den Cache gestellt. Sie wurde ausschließlich für die Ausführung mit OPTION (RECOMPILE) verwendet. Und sie hat KEINE Auswirkungen auf zukünftige Ausführungen. Wenn ich zurück gehe und ohne OPTION (RECOMPILE) ausführe, dann erhalte ich den vorherigen Plan (um den Index zu verwenden).

Nun, die anderen beiden Fragen – diese sind viel interessanter und hier denke ich, dass Stored Procedures verwendet werden sollten. Ich persönlich denke, dass Entwickler, die die Daten und die Anwendung kennen, viel besser in der Lage sind, den RICHTIGEN Code zu erstellen, vor allem, wenn sie alle Optionen verstehen, die ihnen zur Verfügung stehen.

Hier ist die Art und Weise, wie ich über Stored Procedures und Rekompilierung denke:

• Wenn ich weiß, dass eine bestimmte Anweisung immer die gleiche Anzahl von Zeilen zurückgibt und den gleichen Plan verwendet (und ich würde das aus Tests wissen), dann erstelle ich die Stored Procedure normal und lasse den Plan zwischengespeichert werden.
• Wenn ich weiß, dass eine bestimmte Anweisung von Ausführung zu Ausführung stark variiert und der optimale Plan variiert (auch dies sollte ich aus dem Testen mehrerer Beispielausführungen wissen), dann erstelle ich die Stored Procedure normal und verwende OPTION (RECOMPILE), um sicherzustellen, dass der Plan der Anweisung nicht mit der Stored Procedure gecached oder gespeichert wird. Bei jeder Ausführung erhält die Stored Procedure andere Parameter und die besonders unangenehme Anweisung erhält bei jeder Ausführung einen neuen Plan.

Allerdings ist das auch der Punkt, an dem die Dinge schwieriger werden. Ich habe schon oft Stored Procedures gesehen, bei denen die Leute versucht haben, die verschiedenen Arten von Parametern mit bedingter Logik aufzuteilen, und das funktioniert in der Regel nicht so gut wie erwartet (darüber werde ich als nächstes bloggen). Und das ist immer der Punkt, an dem sich einige entscheiden, dass sie die Anweisung, die ausgeführt wird, dynamisch aufbauen wollen – jetzt müssen wir bestimmen, ob wir sp_executesql oder EXEC verwenden sollen oder nicht. Und an dieser Stelle gibt es wirklich ein paar Optionen. In einem oder zwei weiteren Beiträgen werde ich Ihnen schließlich zeigen, wo EXEC ein klarer Gewinner gegenüber sp_executesql ist, denn selbst die OPTION (RECOMPILE) hilft nicht immer bei ALLEN Arten von Plänen (und insbesondere bei einer der häufigeren Arten von Plänen, die ich sehe).