Original von Tibor Karaszi; deutsche Übersetzung von Frank Kalis

Überblick
Die Verwendung Gespeicherter Prozeduren wird allgemein als gute Praktik betrachtet. Einer der Vorteile Gespeicherter Prozeduren ist deren Vorkompilierung. Dies bedeutet, dass zur Ausführungszeit SQL Server den vorkompilierten Ausführungsplan der Gespeicherten Prozedur aus dem Cache holt und ausführt, falls dieser bereits im Cache vorhanden ist. Dies ist generell schneller als den Code bei jeder Ausführung zu optimieren und kompilieren. Unter bestimmten Umständen jedoch, ist es jedoch notwendig, eine Prozedur während der Ausführung neu zu kompilieren.

Weitere Informationen
Die Engine, die für die Ausführung zuständig ist, kann an einen Punkt der Gespeicherten Prozedur kommen, an dem die Entscheidung getroffen wird, die gesamte Prozedur neu zu kompilieren. Beachten Sie, dass Gespeicherte Prozeduren nicht Statement für Statement kompiliert werden. Wenn eine Neukompilierung notwendig ist, zum Beispiel am Ende der Prozedur, dann wird die gesamte Prozedur optimiert und kompiliert. Warum? Hier sind einige Gründe:

• SQL Server greift auf eine Tabelle zu und bemerkt, dass eine stark abweichende Anzahl an Zeilen jetzt in der Tabelle existieren verglichen zu dem Zeitpunkt, als der Prozedurplan ursprünglich erstellt wurde. In so einem Szenario können die Hints KEEP PLAN und KEEPFIXED PLAN hilfreich sein.
• DDL nach DML. Angenommen, Sie erstellen eine temporäre Tabelle und einen Index auf diese temporäre Tabelle. Wird die Prozedur erstmalig kompiliert, hat dies noch nicht stattgefunden. Daher könnte SQL Server entscheiden, die Prozedur bei Ausführung neu zu kompilieren, wenn zu einem späteren Zeitpunkt ein SELECT Statement auftaucht, welches diese temporäre Tabelle verwendet, um den optimalen Ausführungsplan für dieses SELECT zu ermitteln.
• Sie verwenden ein SET Statement in der Prozedur, das die Art und Weise verändert, wie SQL Server die danach folgenden Statements innerhalb der Prozedur verarbeitet. Hierauf bezieht sich dieser Artikel.

Details Bedenken Sie, dass eine Prozedur während der Ausführung durchaus mehrfach neu kompiliert werden kann. Ich würde Ihnen empfehlen, dass Sie mit Profiler das SP:Recompile Ereignis beobachten, um herauszufinden, ob Ihre Prozedur während der Ausführung neu kompiliert wird. Für jede Kompilierung wird dieses Ereignis ausgelöst.
Nicht alle SET Optionen verursachen Neukompilierung. Also habe ich einen Test durchgeführt, um herauszufinden, welche dies sind. Nachfolgend finden Sie eine Tabelle die alle dokumentierten SET Optionen listet zusammen mit dem Hinweis, ob diese Option eine Neukompilierung auslöst oder nicht.

Option	Verursacht Neukompilierung	Kommentar
SET ANSI_DEFAULTS OFF	Ja
SET ANSI_NULLS OFF	Ja	Verursacht Neukompilierung, auch wenn es sich nur um eine dummy Operation handelt
SET ANSI_PADDING OFF	Ja
SET ANSI_WARNINGS OFF	Ja
SET ARITHABORT OFF	Ja
SET CONCAT_NULL_YIELDS_NULL OFF	Ja	Verwenden Sie ISNULL() oder COALESCE stattdessen.
SET DATEFORMAT dmy	Ja	Verwenden Sie lieber ein sprachneutrales Format als diese Option.
SET FORCEPLAN o­n	Ja
SET LANGUAGE svenska	Ja	Verwenden Sie lieber ein sprachneutrales Format als diese Option.
SET NUMERIC_ROUNDABORT o­n	Ja
SET ARITHIGNORE o­n	Nein
SET CURSOR_CLOSE_ON_COMMIT o­n	Nein
SET DATEFIRST 2	Nein
SET DEADLOCK_PRIORITY LOW	Nein
SET DISABLE_DEF_CNST_CHK o­n	Nein
SET IMPLICIT_TRANSACTIONS o­n	Nein
SET LOCK_TIMEOUT 200	Nein
SET NOCOUNT o­n	Nein
SET QUERY_GOVERNOR_COST_LIMIT 30	Nein
SET REMOTE_PROC_TRANSACTIONS o­n	Nein
SET ROWCOUNT 30	Nein
SET TEXTSIZE 3000	Nein
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ	Nein
SET XACT_ABORT o­n	Nein
SET IDENTITY_INSERT o­n	n/a	Nicht getestet.
SET QUOTED_IDENTIFIER	n/a	Einstellung wird vom CREATE Zeitpunkt vererbt. Nicht getestet.
SET FMTONLY	n/a	Selten genutzte Option. Nicht getestet.
SET NOEXEC	n/a	Selten genutzte Option. Nicht getestet.
SET PARSEONLY	n/a	Selten genutzte Option. Nicht getestet.
SET SHOWPLAN_ALL	n/a	Selten genutzte Option. Nicht getestet.
SET SHOWPLAN_TEXT	n/a	Selten genutzte Option. Nicht getestet.
SET STATISTICS IO	n/a	Selten genutzte Option. Nicht getestet.
SET STATISTICS PROFILER	n/a	Selten genutzte Option. Nicht getestet.
SET STATISTICS TIME	n/a	Selten genutzte Option. Nicht getestet.
SET ANSI_NULL_DFLT_OFF	n/a	Selten genutzte Option. Nicht getestet.
SET ANSI_NULL_DFLT_ON	n/a	Selten genutzte Option. Nicht getestet.
SET FIPS_FLAGGER	n/a	Selten genutzte Option. Nicht getestet.

SQL Code
Ich habe folgenden SQL Code bei obigem Test verwendet:

USE pubs
SET NOCOUNT ON
GO
--Create dummy proc so we can do ALTER PROC when we generate the proc
CREATE PROC p AS
PRINT 'dummy'
GO
--Create table to hold the SET parameters to set:
CREATE TABLE ct (cn varchar(100) PRIMARY KEY)
--Populate the table
INSERT ct VALUES('SET DATEFIRST 2')
INSERT ct VALUES('SET DATEFORMAT dmy')
INSERT ct VALUES('SET DEADLOCK_PRIORITY LOW')
INSERT ct VALUES('SET LOCK_TIMEOUT 200')
INSERT ct VALUES('SET CONCAT_NULL_YIELDS_NULL OFF')
INSERT ct VALUES('SET CURSOR_CLOSE_ON_COMMIT ON')
INSERT ct VALUES('SET DISABLE_DEF_CNST_CHK ON')
INSERT ct VALUES('SET LANGUAGE svenska')
INSERT ct VALUES('SET ARITHABORT OFF')
INSERT ct VALUES('SET ARITHIGNORE ON')
INSERT ct VALUES('SET ANSI_NULLS OFF')
INSERT ct VALUES('SET NOCOUNT OFF')
INSERT ct VALUES('SET NUMERIC_ROUNDABORT ON')
INSERT ct VALUES('SET QUERY_GOVERNOR_COST_LIMIT 30')
INSERT ct VALUES('SET ROWCOUNT 30')
INSERT ct VALUES('SET TEXTSIZE 3000')
INSERT ct VALUES('SET ANSI_DEFAULTS OFF')
INSERT ct VALUES('SET ANSI_PADDING OFF')
INSERT ct VALUES('SET ANSI_WARNINGS OFF')
INSERT ct VALUES('SET FORCEPLAN ON')
INSERT ct VALUES('SET IMPLICIT_TRANSACTIONS ON')
INSERT ct VALUES('SET REMOTE_PROC_TRANSACTIONS ON')
INSERT ct VALUES('SET TRANSACTION ISOLATION LEVEL READ COMMITTED')
INSERT ct VALUES('SET XACT_ABORT ON')


--Create the proc which in turns create a proc with the various SET parameters inside.
--Then debug this proc, set a breakpoint and execute the proc named "p" for
--each iteration and see if it triggered a recompile.
CREATE PROC generate_proc AS
DECLARE @sql nvarchar(2000), @set_option nvarchar(100)
DECLARE c CURSOR FOR SELECT cn FROM ct ORDER BY cn
OPEN c
FETCH NEXT FROM c INTO @set_option
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = 'ALTER PROC p AS' + CHAR(13) + CHAR(10)
SET @sql = @sql + @set_option + CHAR(13) + CHAR(10)
SET @sql = @sql + 'SELECT au_fname, au_lname FROM authors WHERE au_lname = ''White''' + CHAR(13) + CHAR(10)
PRINT @set_option
EXEC(@sql)
FETCH NEXT FROM c INTO @set_option
END
CLOSE c
DEALLOCATE c


Den Originalartikel finden Sie hier.