Problembehandlung bei der Neukompilierung von gespeicherten Prozedur

SPRACHE AUSWÄHLEN SPRACHE AUSWÄHLEN
Artikel-ID: 243586 - Produkte anzeigen, auf die sich dieser Artikel bezieht
Alles erweitern | Alles schließen

Auf dieser Seite

Zusammenfassung

Dieser Artikel befasst sich mit einem bestimmten Leistungsproblem, die Anwendungen mit Microsoft SQL Server auftreten können: die Laufzeit-Neukompilierung von gespeicherten Prozeduren. Bei Sie sind ein Leistungsproblem behandeln, aber Sie nicht festgestellt haben, dass dies die genaue Ursache des Problems ist, finden Sie die folgenden Artikel in der Microsoft Knowledge Base vor:

224587So wird 's gemacht: Behandlung von Anwendungsleistung mit SQLServer
In diesem Artikel wird davon ausgegangen, dass Sie in diesem Artikel verwendet haben, um den Umfang des Problems einzugrenzen und, Sie eine SQL Server Profiler-Ablaufverfolgung mit der bestimmten Ereignisse und Datenspalten darin ausführliche erfasst haben.

Weitere Informationen

Wenn ein Benutzer eine gespeicherte Prozedur, ausführt wenn er noch nicht im Cache verfügbar ist, wird SQL Server lädt die Prozedur und Abfrage-Plan kompiliert. Kompilierte Plan wird im Cache gespeichert und durch nachfolgende Aufrufer der gespeicherten Prozedur wiederverwendet, bis einige Aktion für ungültig erklärt den Plan und erzwingen eine Neukompilierung auftritt. Die folgenden Aktionen verursachen möglicherweise Neukompilierung einer gespeicherten Prozedur Plan:
  • Eine WITH RECOMPILE-Klausel in der CREATE PROCEDURE oder EXECUTE-Anweisung verwendet.
  • Schema Änderungen bei einem der referenzierten Objekte, einschließlich hinzufügen oder Löschen von Einschränkungen, Standards oder Regeln.
  • Sp_recompile für eine Tabelle verweist die Prozedur ausgeführt.
  • Wiederherstellen der Datenbank mit der Prozedur oder die Objekte verweist, auf die Prozedur (falls Sie datenbankübergreifende Vorgänge durchführen).
  • Ausreichende Serveraktivität verursacht Planes, der außerhalb des Cache verfallen sein.
Diese Gründe für die Neukompilierung einer gespeicherten Prozedur in früheren Versionen vorhanden, und verursacht den Plan vor Ausführung der Prozedur erneut kompilieren. In SQL Server 7.0 wird ein neues Verhalten eingeführt, die möglicherweise einer gespeicherten Prozedur während der Ausführung neu kompiliert. Wird dieses neue Verhalten sichergestellt, dass der Optimierer immer den am besten möglichen Plan auch für jede bestimmte Anweisung innerhalb einer Prozedur. Die folgenden Ereignisse verursachen möglicherweise eine Laufzeit-Neukompilierung einer gespeicherten Prozedur:
  • Ausreichende Prozentwert eines Datenänderungen in einer Tabelle, die von der gespeicherten Prozedur verwiesen wird.
  • Die Prozedur interleaves Operationen DDL (Data Definition Language) und DML (Data Manipulation Language).
  • Die Prozedur führt bestimmte Operationen auf temporäre Tabellen.
Dieser Ursachen werden weitere in diesem Artikel ausführlich erläutert.

In einigen Fällen sind die Kosten des erneuten Kompilieren der gespeicherten Prozedur mehr als der Vorteil dabei, insbesondere bei großen Prozeduren abgeleitet. Es ist sehr wichtig zu beachten, dass wenn eine Neukompilierung ausgelöst wird, erneut die gesamte Stapel oder die Prozedur kompiliert ist. Dies bedeutet, dass Leistungseinbußen direkt proportional zur Größe der Prozedur oder Batch ist. Weitere Informationen zu diesem Thema finden Sie "Transact-SQL-Tipps" in der SQL Server-Onlinedokumentation.


Die folgende Informationen in diesem Artikel konzentriert sich auf Identifizieren der Ursache von Neukompilierungen der gespeicherten Prozedur zur Laufzeit und beschreibt Methoden, mit denen Sie verhindern.

Bewährte Methoden

Es empfiehlt sich, Besitzer Prozedurnamen qualifizieren, beim Ausführen einer Prozedur. Dies ermöglicht eine bessere Übersichtlichkeit und einfacher Wiederverwendung von vorhandenen Ausführungsplan vom aktuellen Benutzer. Z. B. Wenn ein Benutzer nicht der Datenbankbesitzer (Dbo) eine Dbo im Besitz gespeicherte Prozedur (in diesem Beispiel wird als namens MyProc bezeichnet) in der Pubs -Datenbank ausführt, verwenden Sie die folgende Anweisung:
exec dbo.myProc
				
statt dies:
exec myProc
				
dieses Verfahren beseitigt Verwirrungen andere möglichen Versionen der Prozedur nach verschiedenen Besitzern vom Standpunkt der Codierung und Wartung und darüber hinaus ermöglicht SQL Server den Ausführungsplan für die bestimmte Prozedur mehr direkt zugreifen.

Indem Sie den Namen des Besitzers nicht qualifizieren, SQL Server gibt den Code kompilieren und erhält eine Sperre COMPILE für die Prozedur. Wird jedoch schließlich es festgelegt, dass ein neuer Plan nicht erforderlich ist (sofern keine andere Gründe anwenden), so dass den Plan zu diesem Zeitpunkt aufgrund von der Mangel an Qualifikation nicht neu kompiliert wird. Zusätzliche Schritt von eine COMPILE-Sperre für die Prozedur kann jedoch blockieren Konflikte in schwerwiegenden Fällen führen. Verweisen Sie auf Q263889 INF: SQL blockieren aufgrund [COMPILE] Sperren für weitere Einzelheiten zu dieser Situation.

Wenn Sie Besitzer den Prozeduraufruf mit owner.procedure qualifizieren, müssen Sie die Sperre kompilieren zu erhalten, damit der Konflikt reduziert wird.

Identifizieren und Lösen von Problemen

Falls nicht bereits geschehen, finden Sie im folgenden Artikel in der Microsoft Knowledge Base ausführliche Informationen zum Erfassen von Profiler-Daten zu helfen, die Leistung des Systems analysieren:
224587So wird 's gemacht: Behandlung von Anwendungsleistung mit SQLServer

Anzeigen der Profiler-Daten

SQL Server Profiler enthält ein SP: RECOMPILE Ereignis, das Sie verwenden können, um die Anzahl der Neukompilierungen auftreten zu überwachen. Das SP: RECOMPILE -Ereignis tritt ein, wenn während der Ausführung eine gespeicherte Prozedur neu kompiliert.
  • Gruppe der Profiler-Ablaufverfolgung nach Ereignisklasse

    1. Klicken Sie im Menü Datei auf Eigenschaften .
    2. Verwenden Sie auf der Registerkarte Datenspalten die auf Schaltfläche, um die Ereignisklasse und Text unter der Überschrift Gruppen mit Event-Klasse zu verschieben, zuerst. Verwenden Sie die ab-Schaltfläche, um alle anderen Spalten unter der Überschrift Gruppen zu entfernen.
    3. Klicken Sie auf OK .
    Überprüfen der Anzahl der SP: RECOMPILE -Ereignisse.

    Sie können die SP: RECOMPILE-Gruppe, die Details der einzelnen Vorkommen anzuzeigen erweitern. Die Spalte Text des Ereignisses gibt den Namen der gespeicherten Prozedur, die neu kompiliert. Wenn mehrere Prozeduren Neukompilierungen verursacht werden, werden Sie durch die Anzahl der Vorkommen sortiert. Wenn Sie eine große Anzahl von SP: RECOMPILE -Ereignisse und Sie hohen CPU-Auslastung treten, den Fokus zum Auflösen von Prozeduren, die die höchste Anzahl von Neukompilierungen enthalten. Notieren Sie Systemprozess-ID (SPID) und Startzeit des SP: RECOMPILE -Ereignisses für eine Instanz von bestimmten gespeicherten Prozedur, und gehen Sie folgendermaßen vor.

    Wenn keine SP: RECOMPILE -Ereignisse angezeigt, jedoch ein Leistungsproblem weiterhin auftreten, finden Sie im folgenden Artikel der Microsoft Knowledge Base:
    224587So wird 's gemacht: Behandlung von Anwendungsleistung mit SQLServer
  • bestimmen Sie die Anweisung, die das Kompilieren-Ereignis ausgelöst

    1. Klicken Sie im Menü Datei auf Eigenschaften .
    2. Verwenden Sie auf der Registerkarte Datenspalten die ab-Schaltfläche, um alle anderen Spalten unter der Überschrift Gruppen zu entfernen.
    3. Entfernen Sie auf die Registerkarte Ereignisse alle Ereignisse außer SP: Starting , SP: StmtStarting , SP: RECOMPILE und SP: abgeschlossen . Wenn SP: StmtStarting -Ereignisses nicht aufgezeichnet, SP: StmtCompleted ersetzen kann, aber keine enthalten beide da dies die Menge an Informationen so verdoppelt Sie durchsuchen müssen aus.
    4. Wenn Sie eine gespeicherte Prozedur Neukompilierung untersuchen eine bestimmte Instanz angegeben haben, können Sie die Daten beschränken, die Sie mithilfe der Registerkarte Filter auf die bestimmte SPID und Zeitrahmen des Vorkommens anzeigen.
    5. Klicken Sie auf OK .

    SP: RECOMPILE -Ereignis wird ausgelöst, direkt nach das SP:StmtStarted -Ereignis der gespeicherten Prozedur-Anweisung, die die Neukompilierung verursacht. Nachdem das Kompilieren-Ereignis abgeschlossen ist, sehen Sie eine Wiederholung des Ereignisses SP:StmtStarted , der angibt, dass die Anweisung mit dem neu generierten Plan ausgeführt wird.

    Im folgenden Beispiel:
    use pubs
    go
    drop procedure RecompProc 
    go
    create procedure RecompProc as
    create table #t (a int)
    select * from #t
    go
    exec RecompProc
    						
    Wenn Sie diesen Code in Query Analyzer ausführen und die oben genannten Ereignisse in eine Profiler-Ablaufverfolgung anzeigen, sehen Sie die folgende Sequenz:

    Tabelle minimierenTabelle vergrößern
    Ereignisklasse Text
    SP: StartingRecompProc
    SP: StmtStartingErstellen der Tabelle # t (Int)
    SP: StmtStartingWählen Sie * von # t
    SP: RECOMPILERecompProc
    SP: StmtStartingWählen Sie * von # t
    SP: CompletedRecompProc


    Sie können sofort erkennen, wurde die Anweisung, die die Neukompilierung verursacht:
    select * from #t
    						
    Da es vor und nach dem SP: RECOMPILE -Ereignis angezeigt wird.

    Wenn Sie nur die SP: StmtCompleted -Ereignis, aber nicht das SP: StmtStarting -Ereignis erfasst hatten, wird die SP: RECOMPILE direkt vor der Anweisung angezeigt, die es als unten verursacht:

    Tabelle minimierenTabelle vergrößern
    Ereignisklasse Text
    SP: StartingRecompProc
    SP: RECOMPILERecompProc
    SP: StmtCompletedWählen Sie * von # t
    SP: CompletedRecompProc


    Sehen Sie, dass vor dem SP: StmtCompleted -Ereignis für das SP: RECOMPILE -Ereignis ausgelöst ist die "Wählen Sie * von # t"-Anweisung, die die Neukompilierung verursacht. Dies ist sinnvoll, wie die Anweisung bis nicht abgeschlossen werden kann, nachdem der neue Abfrageplan für das Kompilieren generiert wird. Die restlichen Beispiele in diesem Artikel verwenden SP: StmtStarting -Ereignisses. Wenn Sie nur das SP: StmtCompleted -Ereignis erfasst haben, denken Sie an die Anweisung nach der SP: RECOMPILE wie oben erläutert Sie.

    Beachten Sie, wenn Sie diese bestimmte gespeicherte Prozedur mehrere Male ausführen, SQL Server den vorhandenen Plan für diese Prozedur wiederverwenden. Das Kompilieren-Ereignis wird nur bei der ersten Ausführung der Prozedur angezeigt oder wenn Sie löschen und die Prozedur jedes Mal, wenn Neuerstellen Sie das Skript ausführen. Der Grund für die Neukompilierung in diesem speziellen Fall wird im Abschnitt "Neukompilierungen aufgrund zu überlappen Data Definition Language (DDL) und Data Manipulation Language (DML) Operations" in diesem Artikel erläutert, dies ist lediglich ein Beispiel zu veranschaulichen, wie leicht ermitteln, welche Anweisung die Neukompilierung verursacht.

Neukompilierungen aufgrund von Änderungen der Zeile

Kompilieren Sie wenn ausreichende Prozentsatz der Daten in einer Tabelle auf die von einer gespeicherten Prozedur verwiesen wird, seit der ursprünglichen Abfrageplan generiert wurde geändert wurde, wird SQL Server neu die gespeicherte Prozedur sicherstellen, dass es einen Plan auf Grundlage der aktuellsten statistischen Daten verfügt. Berücksichtigen Sie beispielsweise die folgende gespeicherte Prozedur:
drop procedure RowModifications 
go
create procedure RowModifications as
-- assume SomeTable exists with the same definition as #t, 
-- and has over 1000 rows
create table #t (a int, b char(10))
select * from #t
insert #t select * from SomeTable
select count(*) from #t  where a = 37
go
exec RowModifications
exec RowModifications
				
nach der zweiten Ausführung der Prozedur RowModifications wird angezeigt, die folgenden Ereignisse in Profiler:

Tabelle minimierenTabelle vergrößern
Ereignisklasse Text
SP: StartingRowModifications
SP: StmtStartingErstellen Sie Tabelle # t ein Int (b char(10))
SP: StmtStartingWählen Sie * von # t
SP: StmtStartingFügen Sie # t wählen * aus SomeTable
SP: StmtStarting# t count(*) auswählen, in denen eine = 37
SP: RECOMPILERowModifications
Auto-UpdateStatsein
SP: StmtStarting# t count(*) auswählen, in denen eine = 37
SP: CompletedRowModifications

Hinweis: Die erste Ausführung zeigt außerdem ein SP: RECOMPILE -Ereignis für die "Wählen Sie * von # t"-Anweisung. Der Grund für die Neukompilierung in diesem speziellen Fall wird im Abschnitt "Neukompilierungen aufgrund zu überlappen Data Definition Language (DDL) und Data Manipulation Language (DML) Operations" in diesem Artikel erläutert. Konzentrieren Sie für dieses Beispiel die SP: RECOMPILE oben angezeigt, da es tritt jedes Mal, wenn die Prozedur ausgeführt wird.

In diesem Beispiel "# t count(*) auswählen, in denen eine 37" Ursachen eine Neukompilierung der Prozedur aufgrund der Änderung der Anzahl der Zeilen =, da die Tabelle erstellt wurde. Das Vorhandensein von Auto-UpdateStats -Ereignis wird bestätigt, dass die Neukompilierung aufgrund von Änderungen der Zeile war. Die Spalte Text gibt die Spalte für die die Statistiken geänderten an.

Wenn die # t-Tabelle erstellt wurde, ist die Anzahl der Zeilen 0 (null). Der Plan für die ursprüngliche "Wählen Sie * von # t" wird mit der Anzahl der Zeilen sowie den Plan für die Abfrage "select Count (1)" entwickelt. Bevor die "select count(*)" ausgeführt werden, werden jedoch 1.000 neue Zeilen in der # t-Tabelle eingefügt. Da eine ausreichende Menge an Daten geändert hat, rekompiliert der Optimierer die Prozedur, stellen Sie sicher, dass den effizientesten Plan für die Anweisung wählt. Diese Neukompilierung wird bei jeder Ausführung der gespeicherten Prozedur auftreten, weil das Einfügen von 1.000 Zeilen immer als bedeutende genug, um Neukompilierung rechtfertigen angezeigt werden.

Der SQL Server verwendet, um bestimmen, ob ein Plan kompiliert werden soll-Algorithmus ist der denselben Algorithmus für Statistiken automatisch aktualisieren, wie in der folgenden Artikel der Microsoft Knowledge Base beschrieben:
195565Info: Funktionsweise von SQLServer 7.0 und SQL Server 2000 Autostats
Im obigen Beispiel ist die gespeicherte Prozedur klein genug, die Neukompilierung keinen spürbaren Effekt auf Leistung hätte. Wenn Sie eine große gespeicherte Prozedur, die mehrere Neukompilierungen führt ähnliche Aktivitäten ausführt verfügen, kann allerdings ein Leistungsabfall feststellen.

Die folgenden Methoden vorhanden um entgegenzuwirken Neukompilierungen aufgrund von Änderungen der Zeile:
  • Führen Sie die Verwendung von Sp_executesql -Anweisung.
    Hierbei handelt es sich um das bevorzugte Verfahren. Anweisungen, die mithilfe von Sp_executesql gespeicherten Prozedur ausgeführt werden als Teil der gespeicherten Prozedur Plan nicht kompiliert. Daher wird beim Ausführen der Anweisung SQL Server werden entweder verwenden Sie einen vorhandenen Plan im Cache für die Anweisung oder eine neue erstellen zur Laufzeit. In beiden Fällen der Plan für die aufrufende gespeicherte Prozedur ist nicht betroffen und muss nicht neu kompiliert werden.

    Die EXECUTE-Anweisung hat dieselbe Wirkung; allerdings ist es nicht empfohlen. Mithilfe der EXECUTE-Anweisung ist nicht so effizient wie Sp_executesql verwenden, da es keine Parametrisierung der Abfrage ermöglicht.

    Die oben beschriebenen RowModifications Vorgehensweise kann wie folgt mit Sp_executesql geschrieben werden:

    drop procedure RowModifications2 
    go
    create procedure RowModifications2 as
    set nocount on
    -- assume SomeTable exists with the same definition as #t, 
    -- and has over 1000 rows
    create table #t (a int, b char(10))
    select * from #t
    insert #t select * from SomeTable
    exec sp_executesql N'select count(*) from #t where a = @a', 
                       N'@a int', @a =  37
    go
    exec RowModifications2
    exec RowModifications2
    						

    Für die zweite Ausführung der Prozedur, RowModifications2 sehen Sie die folgenden Ereignisse in Profiler:

    Tabelle minimierenTabelle vergrößern
    Ereignisklasse Text
    SP: StartingRowModifications2
    SP: StmtStartingErstellen Sie Tabelle # t ein Int (b char(10))
    SP: StmtStartingWählen Sie * von # t
    SP: StmtStartingFügen Sie # t wählen * aus SomeTable
    SP: StmtStartingExec Sp_executesql N'select count(*) von # t, wobei eine = @ eine ', N'@a Int ', @ eine 37 =
    SP: Starting
    SP: StmtStarting# t count(*) auswählen, in denen eine = @ ein
    Auto-UpdateStatsein
    SP: StmtStarting# t count(*) auswählen, in denen eine = @ ein
    SP: Completed
    SP: CompletedRowModifications2


    Beachten Sie, dass keine SP: RECOMPILE -Ereignisse für die Prozedur RowModifications2 sind. Sind vollständige SP: Starten , SP: abgeschlossen Ereignisse für Sp_executesql Aufruf Kontext und ein Auto-UpdateStats -Ereignis für eine Spalte. Jedoch, da dieser Aufruf den Kontext der gespeicherten Prozedur ist, die RowModifications2 -Prozedur muss nicht in diesem Fall kompiliert werden.

    Weitere Informationen zur Verwendung von Sp_executesql gespeicherten Prozedur finden Sie in die "Sp_executesql (T-SQL)" und ? Verwenden von Sp_executesql ? Themen in SQL Server-Onlinedokumentation.
  • Verwenden Sie Sub-procedures, um die Anweisungen ausführen, die die Neukompilierungen verursachen.
    In diesem Fall die Anweisung kann eine Neukompilierung weiterhin verursachen, aber anstelle von Neukompilierung der großen aufrufenden gespeicherten Prozedur, wird es nur kleine untergeordnete Prozedur kompilieren.
  • Verwenden Sie die Option KEEP PLAN.
    Temporäre Tabellen verfügen über spezielle Regeln bezüglich der erneuten Kompilierungen, die in einigen Fällen "strengere als der Standardalgorithmus Neukompilierung" sein kann. Sie können die KEEP PLAN Option, um den Schwellenwert temporäre Tabelle an der Standardalgorithmus lockern. Finden Sie weitere Informationen im Abschnitt "Vermeiden Neukompilierung durch Verwenden der KEEP PLAN Option" dieses Artikels.
Hinweis: Das RowModifications -Verfahren ist ein sehr vereinfachtes Beispiel einer Prozedur, die aufgrund von Änderungen für die Zeile erneut kompiliert wird. Überprüfen Sie die folgenden Vorsichtsmaßnahmen bezüglich dieses Beispiel aus:

  • Obwohl das Beispiel eine temporäre Tabelle verwendet wird, gilt diese Situation für gespeicherte Prozeduren, sowie permanente Tabellen verweisen. Wenn eine ausreichende Menge an Daten in einer Bezugstabelle geändert wurde, da der Abfrageplan generiert wurde, werden die gespeicherte Prozedur erneut kompiliert. Die Unterschiede wie temporäre Tabellen gelten für die Neukompilierung Zwecke in "Vermeiden Neukompilierung durch Verwenden der KEEP PLAN Option" beschriebenen Abschnitt dieses Artikels.
  • Die ersten Ausführungen der oben genannten Verfahren darüber hinaus bewirken eine Neukompilierung auf der ersten Auswahl aus der temporären Tabelle # t. Die Gründe für diese Neukompilierung sind in der "Neukompilierungen aufgrund zu überlappen Daten Definition Language (DDL) und Data Manipulation Language (DML) Operations" beschriebenen Abschnitt dieses Artikels.
  • Eine Anweisung "select count(*) von # t" wurde in eine einfache, sondern in diesem Beispiel wird verwendet "Wählen Sie * von # t"-Anweisung. Um eine übermäßige Neukompilierungen zu vermeiden, SQL Server berücksichtigt keine Neukompilierung "trivial Pläne" (z. B. eine Select * aus einer Tabelle) aufgrund von Änderungen der Zeile.

Neukompilierungen aufgrund von Datendefinitionssprache (DDL) und Data Manipulation Language (DML) Vorgänge überlappen

Wenn DDL-Operationen innerhalb einer Prozedur oder die Stapelverarbeitung durchgeführt werden, die Prozedur oder der Stapel wird kompiliert, wenn der ersten nachfolgenden DML entdeckt Auswirkungen auf die Tabelle in der DDL-Operation.

Berücksichtigen Sie die folgenden gespeicherten Beispielprozedur:
drop procedure Interleave 
go
create procedure Interleave as
-- DDL
create table t1 (a int)
-- DML
select * from t1
-- DDL
create index idx_t1 on t1(a)
-- DML
select * from t1
-- DDL
create table t2 (a int)
-- DML
select * from t2
go
exec Interleave
				
Wenn Sie diesen Code in Query Analyzer ausführen und die oben genannten Ereignisse in eine Profiler-Ablaufverfolgung anzeigen, sehen Sie die folgende Sequenz:

Tabelle minimierenTabelle vergrößern
Ereignisklasse Text
SP: StartingÜberlappen
SP: StmtStartingt1-Tabelle erstellen (Int)
SP: StmtStartingWählen Sie * from t1
SP: RECOMPILEÜberlappen
SP: StmtStartingWählen Sie * from t1
SP: StmtStartingIndex idx_t1 auf t1(a) erstellen
SP: StmtStartingWählen Sie * from t1
SP: RECOMPILEÜberlappen
SP: StmtStartingWählen Sie * from t1
SP: StmtStartingErstellen der Tabelle t2 (Int)
SP: StmtStartingWählen Sie * von t2
SP: RECOMPILEÜberlappen
SP: StmtStartingWählen Sie * von t2
SP: CompletedÜberlappen


In diesem Fall wird die gespeicherte Prozedur während der Ausführung erneut dreimal kompiliert. Um zu verstehen, warum dies geschieht, sollten Sie Sie wie der Abfrageoptimierer einen Plan für diese gespeicherte Prozedur entwickelt:
  1. Während der ersten Kompilierung der Prozedur existieren die Tabellen t1 und t2 nicht. Daher kann kein Plan für die Verweise auf diese Tabellen Abfragen erstellt werden. Sie müssen zur Ausführungszeit erzeugt werden.
  2. Wenn die Prozedur zum ersten Mal ausgeführt wird, ist das erste Schritt t1-Tabelle zu erstellen. Die nächste Schritt ist eine Wählen von t1-Tabelle--es kein Plan für ist. Die Prozedur wird daher an dieser Stelle neu entwickeln ein Plans für die SELECT-Anweisung kompiliert. Ein Plan wird für die aktuelle Select from t1 sowie die Select from t1 nach der Indexerstellung generiert. Kein Plan kann für das Wählen von t2 generiert werden, da t2 trotzdem noch nicht existiert.
  3. Die nächste Schritt besteht darin, einen Index für die t1 zu erstellen. Folgende, wählen Sie einen anderen für t1 ausgeführt werden, hat die jetzt einen Plan aus dem ersten kompilieren. Jedoch, da das Schema der t1 geändert wurde, da der Plan generiert wurde, muss die Prozedur neu erneut kompiliert werden zum Generieren eines neuen Plans für die SELECT-Anweisung aus t1. Und da t2 noch nicht vorhanden ist, kann kein Plan für das Wählen von t generiert werden.
  4. Als Nächstes Tabelle t2 erstellt und aus t2 auswählen werden ausgeführt. Da kein Plan für die Anweisung vorhanden ist, wird die Prozedur eine endgültige Zeit erneut kompiliert.
Diese erneuten Kompilierungen, die auf jeder Ausführung der gespeicherten Prozedur auftreten. Ändern Sie die Neukompilierungen zu reduzieren, die Prozedur dazu all DDL Vorgänge zuerst, gefolgt von DML-Vorgänge wie im folgenden gezeigt:
drop procedure NoInterleave 
go
create procedure NoInterleave as
-- All DDL first
create table t1 (a int)
create index idx_t1 on t1(a)
create table t2 (a int)
-- Then DML 
select * from t1
select * from t1
select * from t2
go
exec NoInterleave 
exec NoInterleave
				
Die erste Ausführung der Prozedur NoInterleave werden die folgenden Ereignisse in Profiler anzeigen:

Tabelle minimierenTabelle vergrößern
Ereignisklasse Text
SP: StartingNoInterleave
SP: StmtStartingt1-Tabelle erstellen (Int)
SP: StmtStartingIndex idx_t1 auf t1(a) erstellen
SP: StmtStartingErstellen der Tabelle t2 (Int)
SP: StmtStartingWählen Sie * from t1
SP: RECOMPILENoInterleave
SP: StmtStartingWählen Sie * from t1
SP: StmtStartingWählen Sie * from t1
SP: StmtStartingWählen Sie * von t2
SP: CompletedNoInterleave


In diesem Fall werden alle DDL-Anweisungen ausgeführt voraus. Der Abfrageoptimierer kompiliert diese Prozedur wie folgt:
  1. Während der ersten Kompilierung der Prozedur existieren die Tabellen t1 und t2 nicht. Daher kann kein Plan für die Verweise auf diese Tabellen Abfragen erstellt werden. Sie müssen zur Ausführungszeit erzeugt werden.
  2. Die ersten Schritte die Prozedur führt sind die DDL Operationen, die auf t1 Tabellen t1 und t2 sowie den Index erstellen.
  3. Die nächste Schritt ist die erste Select from t1 Da kein Plan für diese SELECT-Anweisung verfügbar ist, wird die Prozedur erneut kompiliert. Da alle Objekte vorhanden sind, werden die Pläne für alle der SELECT-Anweisungen in der Prozedur zu diesem Zeitpunkt generiert.
  4. Der Rest der Prozedur führt mit der Pläne generiert. Da keine Änderungen an Objekte, auf die verwiesen wird, ist nicht erforderlich, kompilieren Sie die Prozedur weiter.
Hinweis: Die zweiten und nachfolgenden Ausführungen, die vorhandene Abfrage-Plan und den Zwischenspeicher verwenden und führen Sie überhaupt nicht in allen Neukompilierungen führen. Prozeduren, die erstellen, ändern oder Löschen von Tabellen sollten um sicherzustellen, dass alle DDL-Anweisungen am Anfang der Prozedur geändert werden.

Neukompilierungen von auf bestimmte temporäre Tabelle Operationen

Verwendung von temporären Tabellen in einer gespeicherten Prozedur möglicherweise die gespeicherte Prozedur neu kompiliert werden, jedes Mal, wenn die Prozedur ausgeführt wird.

Um dies zu vermeiden, ändern Sie die gespeicherte Prozedur, so dass es die folgenden Anforderungen erfüllt:
  • Alle Anweisungen, die den Namen einer temporären Tabelle enthalten verweisen auf ein in derselben gespeicherten Prozedur und nicht in einer aufrufenden oder aufgerufenen gespeicherten Prozedur oder in eine Zeichenfolge mit der EXECUTE ausgeführt erstellte temporäre Tabelle gespeicherte Prozedur, Anweisung oder Sp_executesql .
  • Nach der temporären Tabelle in der gespeicherten Prozedur oder dem Trigger syntaktisch angezeigt alle Anweisungen, die den Namen einer temporären Tabelle enthalten.
  • Es gibt keine DECLARE CURSOR-Anweisungen, deren SELECT-Anweisungen eine temporäre Tabelle verweisen.
  • Alle Anweisungen, die den Namen einer temporären Tabelle enthalten stehen alle DROP TABLE-Anweisung, die eine temporäre Tabelle verweist.

    DROP TABLE-Anweisungen sind für temporäre Tabellen in einer gespeicherten Prozedur nicht erforderlich. Die Tabellen werden automatisch gelöscht, wenn die Prozedur abgeschlossen wurde.
  • Keine Anweisungen, die eine temporäre Tabelle erstellen (z. B. CREATE TABLE oder SELECT... INTO) in eine Anweisung zur Ablaufsteuerung von wie z. B. Wenn angezeigt... ELSE oder WHILE.

Vermeiden Neukompilierung mit die Option KEEP PLAN

Temporäre Tabelle Verwendung innerhalb gespeicherter Prozeduren führt bestimmte Komplexität für den Abfrageoptimierer. Die Zeilenanzahl und statistischen Informationen der Tabellen können während der gesamten Lebensdauer der Ausführung einer gespeicherten Prozedur erheblich variieren. Um sicherzustellen, dass der Optimierer den besten Plan in allen Fällen zu temporäre Tabellen verwendet, wurde ein spezieller Algorithmus entwickelt, um striktere mit Neukompilierungen werden. Der Algorithmus besagt, dass wenn eine mit einer gespeicherten Prozedur erstellte temporäre Tabelle mehr als sechs Mal geändert wurde, die Prozedur neu kompiliert werden werden wenn die nächste Anweisung, die temporäre Tabelle verweist.

Im folgenden Beispiel:
drop procedure useKeepPlan 
go
create procedure useKeepPlan as
create table #t (a int, b char(3))
select * from #t
-- Make greater than 6 changes to #t
insert #t values (1, 'abc')
insert #t values (2, 'abc')
insert #t values (3, 'abc')
insert #t values (4, 'abc')
insert #t values (5, 'abc')
insert #t values (6, 'abc')
insert #t values (7, 'abc')
-- Now reference #t
select count(*) from #t 
--option (KEEP PLAN)
go
exec useKeepPlan
exec useKeepPlan
				
in diesem Fall wird die folgenden Ereignisse in Profiler für die zweite Ausführung angezeigt:

Tabelle minimierenTabelle vergrößern
Ereignisklasse Text
SP: StartinguseKeepPlan
SP: StmtStartingErstellen der Tabelle # t (Int)
SP: StmtStarting -Die sieben einfügen Anweisungen-
SP: StmtStarting# t1 count(*) auswählen
SP: RECOMPILEuseKeepPlan
SP: StmtStarting# t1 count(*) auswählen
SP: CompleteduseKeepPlan

Die Prozedur wird auf der Auswahl neu kompiliert, die nach sieben Änderungen in die temporäre Tabelle # t.

Diese aggressive Neukompilierung ist hilfreich in Fällen, in denen die Änderungen an der Datenverteilung der temporären Tabelle wirken sich erheblich den optimal-Abfrage-Plan für die Anweisung darauf verweisen auf können. Allerdings können im Fall von großen Prozeduren, die temporäre Tabellen häufig ändern, aber keine bedeutende Weise, die Neukompilierungen langsamer Gesamtleistung führen. Die Option KEEP PLAN der SELECT-Anweisung wurde für diese Situation eingeführt.

KEEP PLAN beseitigt Prozedur Neukompilierungen von mehr als sechs Änderungen auf temporäre Tabellen innerhalb der Prozedur verursacht und kehrt zurück an den standard-Algorithmus für die Neukompilierung aufgrund von Zeile Änderungen oben im Abschnitt "Neukompilierungen fällig in Zeile Änderungen" dieses Artikels beschriebenen zurück. KEEP PLAN verhindert nicht Neukompilierungen vollständig, er verhindert lediglich die Ursachen von mehr als sechs Änderungen auf temporäre Tabellen in der Prozedur verwiesen. Im obigen Beispiel wenn Sie den Kommentar aus der Zeile "Option (KEEP PLAN)" in der gespeicherten Prozedur entfernen wird das SP: RECOMPILE -Ereignis nicht generiert.

Wenn Sie den Kommentar aus der Zeile "Option (KEEP PLAN)" im obigen Code entfernen und ausführen, sehen Sie die folgenden Ereignisse in Profiler:

Tabelle minimierenTabelle vergrößern
Ereignisklasse Text
SP: StartinguseKeepPlan
SP: StmtStartingErstellen der Tabelle # t (Int)
SP: StmtStarting -Die sieben einfügen Anweisungen-
SP: StmtStarting# t1 Option count(*) auswählen (KEEP PLAN)
SP: CompleteduseKeepPlan


Hinweis Es ist keine SP: RECOMPILE -Ereignis.

Neukompilierungen aufgrund von bestimmten SET-Anweisungen in gespeicherten Prozeduren ausgeführt

Die folgenden fünf SET-Optionen sind standardmäßig auf ON festgelegt:
  • ANSI_DEFAULTS
  • ANSI_NULLS
  • ANSI_PADDING
  • ANSI_WARNINGS
  • CONCAT_NULL_YIELDS_NULL
Wenn Sie die SET-Anweisung, eine dieser Optionen auf OFF festgelegt ausführen, werden die gespeicherte Prozedur jeder Ausführung erneut kompiliert. Der Grund dafür ist, dass Ändern dieser Optionen das Abfrageergebnis beeinflussen kann, das die Neukompilierung ausgelöst.

Betrachten Sie den folgenden Beispielcode:
Use pubs
drop procedure test_recompile
go

create procedure test_recompile as
Set ANSI_DEFAULTS OFF
Select au_lname, au_fname, au_id from authors
where au_lname like 'L%'
--Option (Keep Plan)
Go
				
in diesem Fall wird die folgenden Ereignisse in SQL Profiler für jeder Ausführung der gespeicherten Prozedur angezeigt:
+---------------------------------------------------+
| Event Class     | Text                            | 
+---------------------------------------------------+
| SP:Starting     | test_recompile                  | 
+---------------------------------------------------+
| SP:StmtStarting | Set ANSI_DEFAULTS OFF           | 
+---------------------------------------------------+
| SP:StmtStarting | select au_lname, au_fname, au_id| 
+---------------------------------------------------+
| SP:Recompile    | test_recompile                  | 
+---------------------------------------------------+
| SP:StmtStarting | select au_lname, au_fname, au_id| 
+---------------------------------------------------+
| SP:Completed    | test_recompile                  | 
+---------------------------------------------------+
				
ersetzen die SET-Option mit einer der fünf oben aufgeführten Optionen werden die gleichen Ergebnisse angezeigt. Auch, wird nicht mit der Option von Keep Plan hier helfen die Neukompilierung zu vermeiden, da die Ursache für die Neukompilierung von der SET-Anweisung ist.

Es wird empfohlen damit die Neukompilierung nicht um eines dieser fünf SET-Anweisung in einer gespeicherten Prozedur verwenden. Weitere Informationen finden Sie im folgenden Artikel der Microsoft Knowledge Base:
294942PRB: SET CONCAT_NULL_YIELDS_NULL verursachen gespeicherten Prozeduren zu kompilieren
Jedoch als nicht empfohlen, mit der SET-Anweisung die Verbindungsoption auf denselben Wert wie die gespeicherte Prozedur zurückzusetzen es kann außerdem vermeiden, die erneut kompilieren, Ausführen als:
Set ANSI_DEFAULTS OFF

exec test_recompile
				
der SQL Profiler-Ablaufverfolgung werden keine Ereignisse mehr SP: RECOMPILE angezeigt.

Die folgende Tabelle listet einige allgemeinen SET-Anweisungen und unabhängig davon, ob eine erneute Kompilierung führt die SET-Anweisung in einer gespeicherten Prozedur ändern:
Tabelle minimierenTabelle vergrößern
SET-AnweisungKompilieren Sie neu
Set quoted_identifierNein
Set arithabortja
Set ansi_null_dflt_onja
Set ansi_defaultsja
Set ansi_warningsja
Set ansi_paddingja
Set concat_null_yields_nullja
Set numeric_roundabortNein
Set nocountNein
Set rowcountNein
Set xact_abortNein
Set implicit_transactionsNein
Set arithignoreNein
Set lock_timeoutNein
Set fmtonlyNein

Informationsquellen

308737INF: So identifizieren Sie die Ursache des Neukompilierung im SP: RECOMPILE-Ereignis

Informationen zur Verwendung von SQL Server Profiler finden Sie in SQL Server-Onlinedokumentation.

Eigenschaften

Artikel-ID: 243586 - Geändert am: Freitag, 2. November 2007 - Version: 2.5
Die Informationen in diesem Artikel beziehen sich auf:
  • Microsoft SQL Server 7.0 Standard Edition
  • Microsoft SQL Server 2000 Desktop Engine (Windows)
  • Microsoft SQL Server 2000 Personal Edition
  • Microsoft SQL Server 2000 Developer Edition
  • Microsoft SQL Server 2000 Enterprise Edition
  • Microsoft SQL Server 2000 Standard Edition
Keywords: 
kbmt kbinfo KB243586 KbMtde
Maschinell übersetzter Artikel
Wichtig: Dieser Artikel wurde maschinell und nicht von einem Menschen übersetzt. Die Microsoft Knowledge Base ist sehr umfangreich und ihre Inhalte werden ständig ergänzt beziehungsweise überarbeitet. Um Ihnen dennoch alle Inhalte auf Deutsch anbieten zu können, werden viele Artikel nicht von Menschen, sondern von Übersetzungsprogrammen übersetzt, die kontinuierlich optimiert werden. Doch noch sind maschinell übersetzte Texte in der Regel nicht perfekt, insbesondere hinsichtlich Grammatik und des Einsatzes von Fremdwörtern sowie Fachbegriffen. Microsoft übernimmt keine Gewähr für die sprachliche Qualität oder die technische Richtigkeit der Übersetzungen und ist nicht für Probleme haftbar, die direkt oder indirekt durch Übersetzungsfehler oder die Verwendung der übersetzten Inhalte durch Kunden entstehen könnten.
Den englischen Originalartikel können Sie über folgenden Link abrufen: 243586
Microsoft stellt Ihnen die in der Knowledge Base angebotenen Artikel und Informationen als Service-Leistung zur Verfügung. Microsoft übernimmt keinerlei Gewährleistung dafür, dass die angebotenen Artikel und Informationen auch in Ihrer Einsatzumgebung die erwünschten Ergebnisse erzielen. Die Entscheidung darüber, ob und in welcher Form Sie die angebotenen Artikel und Informationen nutzen, liegt daher allein bei Ihnen. Mit Ausnahme der gesetzlichen Haftung für Vorsatz ist jede Haftung von Microsoft im Zusammenhang mit Ihrer Nutzung dieser Artikel oder Informationen ausgeschlossen.

Ihr Feedback an uns

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com