Beheben von Blockierungen durch Kompilierungssperren

Gilt für: Microsoft SQL Server 2005 Standard EditionMicrosoft SQL Server 2005 Developer EditionMicrosoft SQL Server 2005 Enterprise Edition

Zusammenfassung


In Microsoft SQL Server befindet sich im Allgemeinen nur eine Kopie eines Speicherprozedurplans im Cache. Das Erzwingen dieses Vorgangs erfordert die Serialisierung einiger Teile des Kompilierungsprozesses, und diese Synchronisierung wird teilweise mithilfe von Kompilierungssperren durchgeführt. Wenn viele Verbindungen gleichzeitig dieselbe gespeicherte Prozedur ausführen und für diese gespeicherte Prozedur bei jeder Ausführung eine Kompilierungssperre erhalten werden muss, können Sitzungs-IDs (SPIDs) beginnen, sich gegenseitig zu blockieren, während sie jeweils versuchen, eine exklusive Kompilierungssperre für die Objekt.

Im Folgenden finden Sie einige typische Merkmale der Kompilierungsblockierung, die in der Blockierungsausgabe beobachtet werden können:

  • waittype für die blockierte und (normalerweise) blockierende Sitzung SPIDs ist LCK_M_X (exklusiv) und waitresource hat die Form "OBJECT: dbid:object_id [[COMPILE]]", wobei "object_id" die Objekt-ID der gespeicherten Prozedur ist.
  • Blocker haben waittype NULL, Status runnable. Blockees haben Wartetyp LCK_M_X (exklusive Sperre), Status schlafen.
  • Obwohl die Dauer des Sperrvorfalls lang sein kann, gibt es keine einzelne SPID, die die anderen SPIDs für eine lange Zeit blockiert. Es gibt rollende Blockaden. Sobald eine Kompilierung abgeschlossen ist, übernimmt eine andere SPID für einige Sekunden oder weniger die Rolle des Kopfblockers usw.

Die folgenden Informationen stammen aus einer Momentaufnahme von sys.dm_exec_requests während dieser Art der Blockierung:

   session_id  blocking_session_id  wait_type  wait_time  waitresource   ----------  -------------------  ---------  ---------  ----------------------------      221          29                  LCK_M_X    2141       OBJECT: 6:834102 [[COMPILE]]   228          29                  LCK_M_X    2235       OBJECT: 6:834102 [[COMPILE]]    29         214                  LCK_M_X    3937       OBJECT: 6:834102 [[COMPILE]]    13         214                  LCK_M_X    1094       OBJECT: 6:834102 [[COMPILE]]    68         214                  LCK_M_X    1968       OBJECT: 6:834102 [[COMPILE]]   214           0                  LCK_M_X       0       OBJECT: 6:834102 [[COMPILE]] 

In der Spalte waitresource ("6:834102") ist 6 die Datenbank-ID und 834102 die Objekt-ID. Bitte beachten Sie, dass diese Objekt-ID zu einer gespeicherten Prozedur und nicht zu einer Tabelle gehört.

Weitere Informationen


Die Neukompilierung gespeicherter Prozeduren ist eine Erklärung für Kompilierungssperren für eine gespeicherte Prozedur oder einen Trigger. Die Lösung besteht in diesem Fall darin, die Neukompilierungen zu reduzieren oder zu eliminieren. Eine Erläuterung der häufigsten Gründe, warum eine gespeicherte Prozedur möglicherweise neu kompiliert werden muss, und einige nützliche Informationen zur Reduzierung der Häufigkeit von Neukompilierungen finden Sie im folgenden Microsoft Knowledge Base-Artikel:

243586 Neukompilierung gespeicherter Prozeduren

Zusätzliche Szenarien, die zum Kompilieren von Sperren führen:

  1. Gespeicherte Prozedur wird ohne vollqualifizierten Namen ausgeführt
    • Der Benutzer, der die gespeicherte Prozedur ausführt, ist nicht der Besitzer der Prozedur.
    • Der Name der gespeicherten Prozedur ist mit dem Namen des Objektbesitzers nicht vollständig qualifiziert.

    Wenn Benutzer "dbo" beispielsweise das Objekt dbo.mystoredproc und einen anderen Benutzer, "Harry", besitzt, führt diese gespeicherte Prozedur mit dem Befehl "exec mystoredproc" aus, schlägt die anfängliche Cachesuche nach Objektnamen fehl, da das Objekt nicht besitzerqualifiziert ist. (Es ist noch nicht bekannt, ob eine andere gespeicherte Prozedur namens Harry.mystoredproc vorhanden ist. Daher kann SQL Server nicht sicher sein, dass der zwischengespeicherte Plan für dbo.mystoredproc der richtige ausgeführt wird.) SQL Server erhält dann eine exklusive Kompilierungssperre für die Prozedur und trifft Vorbereitungen für die Kompilierung der Prozedur. Dazu gehört das Auflösen des Objektnamens in eine Objekt-ID. Bevor SQL Server den Plan kompiliert, verwendet SQL Server diese Objekt-ID, um eine genauere Suche im Prozedurcache durchzuführen, und kann einen zuvor kompilierten Plan auch ohne Besitzerqualifizierung finden. Wenn ein vorhandener Plan gefunden wird, verwendet SQL Server den zwischengespeicherten Plan wieder und kompiliert die gespeicherte Prozedur nicht. Das Fehlen der Besitzerqualifizierung zwingt SQL Server jedoch, eine zweite Cachesuche durchzuführen und eine exklusive Kompilierungssperre zu erhalten, bevor das Programm feststellt, dass der vorhandene zwischengespeicherte Ausführungsplan wiederverwendet werden kann. Das Abrufen der Sperre und das Ausführen von Suchmaßnahmen und andere Arbeiten, die erforderlich sind, um diesen Punkt zu erreichen, kann zu einer Verzögerung für die Kompilierungssperren führen, die zum Blockieren führt. Dies gilt insbesondere dann, wenn viele Benutzer, die nicht der Besitzer der gespeicherten Prozedur sind, die Prozedur gleichzeitig ausführen, ohne den Namen des Besitzers angeben zu müssen. Beachten Sie, dass selbst wenn SPIDs nicht auf Kompilierungssperren warten, mangelnde Besitzerqualifizierung zu Verzögerungen bei der Ausführung gespeicherter Prozeduren führen und eine unnötig hohe CPU-Auslastung verursachen kann. Die folgende Abfolge von Ereignissen wird in einer SQL Server Extended Event-Sitzung aufgezeichnet, wenn dieses Problem auftritt.

    Ereignisname Text
    rpc_starting mystoredproc
    sp_cache_miss mystoredproc
    sql_batch_starting mystoredproc
    sp_cache_hit mystoredproc
    ... ...

    sp_cache_miss tritt auf, wenn die Cachesuche nach Namen fehlschlägt, aber dann wurde ein übereinstimmender zwischengespeicherter Plan im Cache gefunden, nachdem der Ambiguos-Objektname in eine Objekt-ID aufgelöst wurde und ein sp_cache_hit-Ereignis vorhanden ist. Die Lösung für dieses Problem der Kompilierungssperre besteht darin, sicherzustellen, dass Verweise auf gespeicherte Prozeduren besitzerqualifiziert sind. (Anstelle von exec mystoredprocverwenden Sie exec dbo.mystoredproc.) Obwohl die Besitzerqualifizierung aus Leistungsgründen wichtig ist, müssen Sie das gespeicherte Proc nicht mit dem Datenbanknamen qualifizieren, um die zusätzliche Cachesuche zu verhindern. Blockieren, die durch Kompilierungssperren verursacht werden, können mithilfe von Standardmethoden zur Fehlerbehebung erkannt werden.

  2. Gespeicherte Prozedur wird mit "sp_" vorangestellt

    Wenn Ihr gespeicherter Prozedurname mit dem Präfix "sp_" beginnt und sich nicht in der Masterdatenbank befindet, wird sp_cache_miss angezeigt, bevor der Cache für jede Ausführung getroffen wird, selbst wenn Sie die gespeicherte Prozedur für den Besitzer qualifizieren. Dies liegt daran, dass das sp_-Präfix SQL Server mitteilt, dass es sich bei der gespeicherten Prozedur um eine gespeicherte Systemprozedur handelt und gespeicherte Systemprozeduren unterschiedliche Regeln für die Namensauflösung aufweisen. (Der "bevorzugte" Speicherort befindet sich in der Masterdatenbank.) Die Namen von vom Benutzer erstellten gespeicherten Prozeduren sollten nicht mit "sp_" beginnen.

  3. Gespeicherte Prozedur wird mit einem anderen Fall aufgerufen (oben /unten)

    Wenn eine vom Besitzer qualifizierte Prozedur ausgeführt wird, indem eine andere Groß- oder Kleinschreibung (oben oder niedriger) als die Zum Erstellen verwendet wurde, kann die Prozedur ein CacheMiss-Ereignis auslösen oder eine COMPILE-Sperre anfordern. Schließlich verwendet die Prozedur den zwischengespeicherten Plan und wird nicht neu kompiliert. Die Anforderung einer COMPILE-Sperre kann jedoch manchmal zu einer "Blockierender Kette" führen, wenn es viele SPIDs gibt, die versuchen, dieselbe Prozedur auszuführen, indem sie einen anderen Fall als den Fall verwenden, der zum Erstellen verwendet wurde. Dies gilt unabhängig von der Sortierreihenfolge oder Sortierung, die auf dem Server oder in der Datenbank verwendet wird. Der Grund für dieses Verhalten ist, dass der Algorithmus, der verwendet wird, um die Prozedur im Cache zu finden, auf Hashwerten (für die Leistung) basiert und die Hashwerte sich ändern können, wenn die Groß-/Kleinschreibung anders ist. Die Problemumgehung besteht darin, die Prozedur zu löschen und zu erstellen, indem Sie dieselbe Groß-/Kleinschreibung verwenden, die beim Ausführen der Prozedur durch die Anwendung verwendet wird. Sie können auch sicherstellen, dass die Prozedur von allen Anwendungen ausgeführt wird, indem Sie die richtige Groß- oder Kleinschreibung verwenden (oben oder unten).

  4. Gespeicherte Prozedur wird als Language-Ereignis aufgerufen

    Wenn Sie versuchen, eine gespeicherte Prozedur als Sprachereignis und nicht als RPC auszuführen, muss SQL Server die Sprachereignisabfrage analysieren und kompilieren, feststellen, dass die Abfrage versucht, die jeweilige Prozedur auszuführen, und dann versuchen, einen Plan im Cache für diese Prozedur zu finden. Um diese Situation zu vermeiden, in der SQL Server das Sprachereignis analysieren und kompilieren muss, stellen Sie sicher, dass die Abfrage als RPC an SQL gesendet wird.

    Weitere Informationen finden Sie im Abschnitt "Gespeicherte Systemprozeduren" im Artikel "Erstellen einer gespeicherten Prozedur".

Bekannte Probleme

Hier sind einige bekannte Probleme, die das Zwischenspeichern von Planungen verhindern können:

  • Sie verwenden BLOB-Variablen als Parameter für gespeicherte Prozeduren. Weitere Informationen erhalten Sie, um den Artikel in der Microsoft Knowledge Base anzuzeigen:
    2380435 FIX: Der Abfrageplan für eine gespeicherte Prozedur wird nicht zwischengespeichert, wenn die gespeicherte Prozedur eine BLOB-Variable verwendet und die Variable in einer Zeichenfolgenfunktion in Microsoft SQL Server 2008 verwendet wird.
  • Sie verwenden OPEN SYMMETRIC KEY in einem gespeicherten Prozedur-/Abfragebatch. Weitere Informationen finden Sie im folgenden MSDN-Blogeintrag: