Behandeln von Blockierungsproblemen, die durch Kompilierungssperren verursacht werden

  • Artikel

In diesem Artikel wird beschrieben, wie Sie Durch Kompiliersperren verursachte Blockierungsprobleme behandeln und beheben.

Ursprüngliche Produktversion: SQL Server
Ursprüngliche KB-Nummer: 263889

Zusammenfassung

In Microsoft SQL Server befindet sich in der Regel immer nur eine Kopie eines Plans für gespeicherte Prozeduren im Cache. Um dies zu erzwingen, ist die Serialisierung einiger Teile des Kompilierungsprozesses erforderlich, und diese Synchronisierung erfolgt teilweise mithilfe von Kompiliersperren. Wenn viele Verbindungen gleichzeitig dieselbe gespeicherte Prozedur ausführen und bei jeder Ausführung eine Kompiliersperre für diese gespeicherte Prozedur abgerufen werden muss, können Sitzungs-IDs (SPIDs) einander blockieren, da sie jeweils versuchen, eine exklusive Kompiliersperre für das Objekt zu erhalten.

Im Folgenden sind einige typische Merkmale der Kompilierungsblockierung aufgeführt, die in der blockierenden Ausgabe beobachtet werden können:

  • waittype für die blockierten und (normalerweise) blockierenden Sitzungs-SPIDs ist LCK_M_X (exklusiv) und waitresource hat das Format OBJECT: dbid: object_id [[COMPILE]], wobei object_id die Objekt-ID der gespeicherten Prozedur ist.

  • Blocker haben waittype NULL, status ausgeführt werden können. Blockees haben waittypeLCK_M_X (exklusive Sperre), status schlafen.

  • Obwohl die Dauer des Blockierungsvorfalls lang sein kann, gibt es keine einzelne SPID, die die anderen SPIDs für einen längeren Zeitraum blockiert. Es gibt rollierende Blockierungen. Sobald eine Kompilierung abgeschlossen ist, übernimmt eine andere SPID für mehrere Sekunden oder weniger die Rolle des Hauptblockers usw.

Die folgenden Informationen stammen aus einer Momentaufnahme von sys.dm_exec_requests während dieser Art von 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 waitresource Spalte (6:834102) ist 6 die Datenbank-ID und 834102 die Objekt-ID. Diese Objekt-ID gehört zu einer gespeicherten Prozedur, nicht zu einer Tabelle.

Weitere Informationen

Die Neukompilierung gespeicherter Prozeduren ist eine Erklärung für Kompiliersperren für eine gespeicherte Prozedur oder einen Trigger. Die Lösung besteht in diesem Fall darin, die Neukompilierungen zu reduzieren oder zu beseitigen.

Zusätzliche Szenarien, die zu Kompilierungssperren führen

  1. Die 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 voll qualifiziert.

    Wenn z. B. der Benutzer dbo das Objekt dbo.mystoredproc besitzt und ein anderer Benutzer, Harry, diese gespeicherte Prozedur mit dem Befehl exec mystoredprocausführt, schlägt die anfängliche Cachesuche nach Objektname fehl, da das Objekt nicht besitzerqualifiziert ist. (Es ist noch nicht bekannt, ob eine andere gespeicherte Prozedur namens Harry.mystoredproc vorhanden ist. Daher können SQL Server nicht sicher sein, dass der zwischengespeicherte Plan für dbo.mystoredproc der richtige Ist.) SQL Server erhält dann eine exklusive Kompilierungssperre für die Prozedur und trifft Vorbereitungen zum Kompilieren der Prozedur. Dies schließt das Auflösen des Objektnamens in eine Objekt-ID ein. Bevor SQL Server den Plan kompiliert, verwendet SQL Server diese Objekt-ID, um eine genauere Suche im Prozedurcache durchzuführen und einen zuvor kompilierten Plan auch ohne Besitzerqualifikation zu 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 Kompiliersperre zu erhalten, bevor das Programm bestimmt, dass der vorhandene zwischengespeicherte Ausführungsplan wiederverwendet werden kann. Das Abrufen der Sperre und das Ausführen von Nachschlagevorgängen und anderen Aufgaben, die erforderlich sind, um diesen Punkt zu erreichen, kann zu einer Verzögerung für die Kompilierungssperren führen, die zu einer Blockierung führt. Dies gilt insbesondere, wenn viele Benutzer, die nicht besitzer der gespeicherten Prozedur sind, die Prozedur gleichzeitig ausführen, ohne den Namen des Besitzers anzugeben. Selbst wenn SPIDs nicht angezeigt werden, die auf Kompiliersperren warten, kann eine fehlende Besitzerqualifizierung zu Verzögerungen bei der Ausführung gespeicherter Prozeduren führen und zu einer hohen CPU-Auslastung führen.

    Die folgende Abfolge von Ereignissen wird in einer SQL Server Sitzung für erweiterte Ereignisse 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 Name fehlschlägt, aber dann ein übereinstimmend zwischengespeicherter Plan im Cache gefunden wurde, nachdem der mehrdeutige Objektname in eine Objekt-ID aufgelöst wurde und ein sp_cache_hit Ereignis vorliegt.

    Die Lösung für dieses Problem der Kompiliersperre 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 den gespeicherten Proc nicht mit dem Datenbanknamen qualifizieren, um die zusätzliche Cachesuche zu verhindern.

    Blockierungen, die durch Kompilierungssperren verursacht werden, können mithilfe von Standardmäßigen Methoden zur Problembehandlung von Blockierungen erkannt werden.

  2. Gespeicherte Prozedur erhält das Präfix sp_

    Wenn der Name Ihrer gespeicherten Prozedur mit dem sp_ Präfix beginnt und sich nicht in der master-Datenbank befindet, wird sp_cache_miss vor dem Cachetreffer für jede Ausführung angezeigt, auch wenn Sie die gespeicherte Prozedur als Besitzer qualifizieren. Dies liegt daran, dass das sp_ Präfix SQL Server angibt, dass die gespeicherte Prozedur eine gespeicherte Systemprozedur ist und gespeicherte Systemprozeduren unterschiedliche Regeln für die Namensauflösung aufweisen. (Der bevorzugte Speicherort befindet sich in der master-Datenbank.) Die Namen von vom Benutzer erstellten gespeicherten Prozeduren sollten nicht mit sp_beginnen.

  3. Die gespeicherte Prozedur wird mit einer anderen Groß-/Kleinschreibung (upper /lower) aufgerufen.

    Wenn eine vom Besitzer qualifizierte Prozedur mit einer anderen Groß- oder Kleinschreibung (groß oder klein) ausgeführt wird als bei der Erstellung, 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 Blockierungskette führen, wenn viele SPIDs 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 basiert (für die Leistung), und die Hashwerte können sich ändern, wenn der Fall anders ist.

    Die Problemumgehung besteht darin, die Prozedur zu löschen und zu erstellen, indem Sie denselben Fall verwenden, der beim Ausführen der Prozedur von der Anwendung verwendet wird. Sie können auch sicherstellen, dass die Prozedur in allen Anwendungen ausgeführt wird, indem Sie die richtige Groß-/Kleinschreibung (groß oder klein) verwenden.

  4. Gespeicherte Prozedur wird als Sprachereignis aufgerufen

    Wenn Sie versuchen, eine gespeicherte Prozedur als Sprachereignis statt als RPC auszuführen, müssen SQL Server die Sprachereignisabfrage analysieren und kompilieren, ermitteln, ob die Abfrage versucht, die bestimmte 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 müssen, 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 in der Onlinedokumentation.

References

OPEN SYMMETRIC KEY-Befehl verhindert das Zwischenspeichern von Abfrageplänen.