Beschreibung des SQL Server Sperren verursacht durch Kompilieren Sperren

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

Auf dieser Seite

Zusammenfassung

In Microsoft SQL Server ist nur eine Kopie eines Plans gespeicherte Prozedur im Allgemeinen im Cache zu einem Zeitpunkt. Erzwingen von diese Serialisierung bestimmter Teile des Kompilierungsvorgangs erfordert und die Synchronisation erfolgt teilweise mithilfe von Sperren für die Kompilierung. Wenn viele Verbindungen gleichzeitig dieselbe gespeicherte Prozedur ausgeführt werden und eine Kompilierung Sperre erworben werden muss für die gespeicherte Prozedur immer ausgeführt wird, beginnt die Systemprozess-IDs (SPIDs) gegenseitig blockieren, wie jeder Versuch, eine Kompilierung exklusive Sperre für das Objekt zu erhalten.

Weitere Informationen

Gespeicherte Prozedur Neukompilierung ist eine Erklärung für die Kompilierung von Sperren auf einer gespeicherten Prozedur oder einem Trigger. Die Lösung besteht in diesem Fall zu verringern oder beseitigen die Neukompilierungen. Eine Erläuterung der häufigsten Gründe, die eine gespeicherte Prozedur neu kompiliert werden kann und einige nützliche Informationen zum Verringern der Häufigkeit von Neukompilierungen finden Sie im folgenden Artikel der Microsoft Knowledge Base:
243586Problembehandlung bei der Neukompilierung von gespeicherten Prozedur
Ein anderes Szenario, in dem Kompilieren Sperren auftreten, ist, wenn die folgenden Bedingungen erfüllt sind:
  • Der Benutzer, der die gespeicherte Prozedur ausgeführt wird, ist nicht der Besitzer der Prozedur.
  • Name der gespeicherten Prozedur ist nicht voll gekennzeichneten Namen der Objektbesitzer.
Beispielsweise, wenn der Benutzer "Dbo" Objekt besitzt.dbo.MyStoredProcund "Paul", ein anderer Benutzer führt diese gespeicherte Prozedur durch Verwenden des Befehls "Exec Mystoredproc," die anfängliche Cachelookup vom Objekt namens fehlschlägt, da das Objekt nicht Besitzer gekennzeichnete. (Es ist noch nicht bekannt, ob eine andere gespeicherte Prozedur mit dem Namen Harry.mystoredproc vorhanden ist. Aus diesem Grund können SQL Server nicht sicher sein, dass der zwischengespeicherte Plan für dbo.mystoredproc richtig ausführen ist.) SQL Server erhält eine Kompilierung exklusive Sperre für die Prozedur dann und macht die Vorbereitungen, um die Prozedur zu kompilieren. Dazu gehören zum Auflösen der Objektname in eine Objekt-ID. Bevor Sie SQL Server, den Plan kompiliert, wird SQL Server verwendet dieses Objekt-ID für eine präzisere Suche des Prozedurcaches und einen bereits kompilierten Plan auch ohne Qualifikation Besitzer suchen können.

Wenn ein vorhandener Plan gefunden wird, wird SQL Server verwendet den zwischengespeicherten Plan und die gespeicherte Prozedur kann tatsächlich nicht kompiliert. Der Mangel an Besitzer Qualifikation erzwingt jedoch SQL Server einen zweiten Cachelookup durchführen und eine Kompilierung exklusive Sperre zu erhalten, bis das Programm feststellt, dass der zwischengespeicherten Ausführungsplan wiederverwendet werden kann. Die Sperre und Durchführen von Suchvorgängen und anderen arbeiten, die benötigt werden, um diesen Punkt erreichen können eine Verzögerung für die Kompilierung Sperren einführen, die zu blockierende führt. Dies ist insbesondere dann, wenn viele Benutzer, die nicht gleichzeitig führen Sie die Prozedur ohne Angabe der Name des Besitzers der gespeicherten Prozedur-Besitzer sind. Bedenken Sie, dass selbst wenn SPIDs kompilieren Sperren warten nicht angezeigt wird, Mangel an Besitzer Qualifikation kann Verzögerungen bei der Ausführung einer gespeicherten Prozedur einführen und unnötig hohen CPU-Auslastung verursachen.

Wenn dieses Problem auftritt, wird die folgende Sequenz von Ereignissen in einer SQL Server Profiler-Ablaufverfolgung aufgezeichnet. (Cache-bezogene Ereignisse verfolgen zu können, müssen Sie erweiterte Ereignisse aktivieren. Klicken Sie dazu im Menü <uiterm>Extras</uiterm> aufOptionenim MenüToolsMenü, und wählen SieAlle Ereignisklassen.)

Tabelle minimierenTabelle vergrößern
EreignisklasseText
RPC: StartingMyStoredProc
SP: CacheMissMyStoredProc
SP:ExecContextHitMyStoredProc
SP: StartingMyStoredProc
......

SP: CacheMissTritt auf, wenn die Cache-Suche nach Namen fehlschlägt. Der folgende CodeSP:ExecContextHitGibt an, dass ein übereinstimmender zwischengespeicherter Plan letztendlich im Cache gefunden wurde, nach der mehrdeutigen Objektnamen aufgelöst, auf eine Objekt-ID. Abhängig von den UmständenSP: CacheHitmöglicherweise anstelle von angezeigt.SP:ExecContextHit.

Die Lösung dieses Problems kompilieren Sperren besteht darin, sicherzustellen, dass Verweise auf gespeicherte Prozeduren Besitzer gekennzeichnet sind. (Anstelle vonExec mystoredproc, Exec verwendendbo.MyStoredProc.) Besitzer Qualifikation aus Leistungsgründen wichtig ist, müssen Sie die gespeicherte Prozedur mit dem Datenbanknamen, um zu verhindern, dass weitere Cachelookup zu qualifizieren.

Blockiert, die Kompilierung entsteht durch Sperren erkannt werden können, mithilfe von blockierenden Skripts wie diejenigen, die in den folgenden Microsoft Knowledge Base-Artikeln definiert sind:
251004INF: Überwachen von SQL Server 7.0-Blockierungen
271509Info: How zum Überwachen von SQL Server 2000 blockieren
Im folgenden werden einige typische Merkmale der Kompilierung zu blockieren, die in der Skriptausgabe blockieren beobachtet werden können:
  • lastwaittypefür die blockierte und (normalerweise) blockierende SPIDs ist LCK_M_X (exklusiv) undwaitresourcedie Form "TAB: Dbid:OBJECT_ID[[KOMPILIERT]]", wobei ?Object_id" die Objekt-ID der gespeicherten Prozedur ist.
  • Popupblocker habenWartetyp0 x 0000 Status ausführbar. Blockees habenWartetyp0x000E (exklusive Sperre) Status im Ruhezustand.
  • Obwohl die Dauer des Vorfalls blockieren möglicherweise lang ist, ist kein einzelnes SPID, die die anderen SPIDs für längere Zeit blockiert. Paralleles blockiert ist. Die Rolle des Head-Blocker für ein paar Sekunden oder weniger usw., sobald eine Kompilierung abgeschlossen ist, übernimmt eine andere SPID.
Die folgende Informationen stammen aus einen Snapshot dersysprocessesBei dieser Art der Sperrung:
   spid  blocked  waittype  waittime  lastwaittype  waitresource
   ----  -------  --------  --------  ------------  -------------------------
   
   221    29      0x000e    2141      LCK_M_X       TAB: 6:834102 [[COMPILE]]
   228    29      0x000e    2235      LCK_M_X       TAB: 6:834102 [[COMPILE]]
    29   214      0x000e    3937      LCK_M_X       TAB: 6:834102 [[COMPILE]]
    13   214      0x000e    1094      LCK_M_X       TAB: 6:834102 [[COMPILE]]
    68   214      0x000e    1968      LCK_M_X       TAB: 6:834102 [[COMPILE]]
   214     0      0x0000       0      LCK_M_X       TAB: 6:834102 [[COMPILE]]
Klicken Sie im FeldwaitresourceSpalte ("6:834102") 6 ist die Datenbank-ID und 834102 ist die Objekt-ID. Denken Sie daran, dass dieses Objekt-ID einer gespeicherten Prozedur, nicht zu einer Tabelle (trotz der Sperrentyp "TAB") gehört.

Hinweise
  • Wenn Sie SQL Server 2005 verwenden, werden viele der Systemtabellen von SQL Server 2000 jetzt als eine Reihe von Ansichten implementiert. Diese Ansichten werden als Kompatibilitätsansichten bezeichnet, und Sie sind nur für Abwärtskompatibilität gedacht. Die Kompatibilitätsansichten verfügbar machen, die gleiche Metadaten, die in SQL Server 2000 verfügbar war. Weitere Informationen über die Zuordnung zwischen den Systemtabellen von SQL Server 2000 und SQL Server 2005-Systemansichten finden Sie unter das Thema "Zuordnung SQL Server 2000 System Tabellen in SQL Server 2005-System Sichten" in der Onlinedokumentation zu SQL Server 2005.
  • Wenn der Name der gespeicherten Prozedur beginnt mit dem Präfix "Sp_" und befindet sich nicht in der master-Datenbank,SP: CacheMissBevor Sie den Cache Treffer für jede Ausführung, auch wenn kennzeichnen Besitzer-Sie die gespeicherte Prozedur. Dies ist da das Präfix Sp_, teilt SQL Server, dass die gespeicherte Prozedur eines Systems ist die gespeicherte Prozedur und gespeicherte Systemprozeduren haben unterschiedliche Namen Regeln. ("Bevorzugte" Speicherort wird in der master-Datenbank.) Die Namen von Benutzern erstellten gespeicherten Prozeduren sollten nicht mit "Sp_" beginnen.
  • Wenn eine Besitzer gekennzeichnete Prozedur mit einer anderen Fall ausgeführt wird, als die Besitzer gekennzeichnete Prozedur erstellt wurde, die Besitzer gekennzeichnete Prozedur erhalten eineCacheMissor request a COMPILE lock but eventually use the cached plan. Therefore, this would not actually recompile the procedure and should not cause much of an overhead. But in certain situations, the request for a COMPILE lock can cause a "blocking chain" situation if there are many SPIDs trying to execute the same procedure with a different case than the procedure was created as. This is true regardless of the sort order or collation that is being used on the server or on the database. The reason for this behavior is that the algorithm that is being used to find the procedure in cache is based on hash values (for performance reasons), which can change if the case is different.

    The workaround is to drop and create the procedure with the same case as the procedure is executed by the application. You can also make sure that the procedure is executed from all applications that use the same case.
  • If you try to execute a stored procedure as a Language Event instead of as an RPC, SQL Server must parse and compile the language event query, determine that the query is trying to execute the particular procedure, and then try to find a plan in cache for that procedure. To avoid this situation in which SQL Server must parse and compile the language event, make sure that the query is sent to SQL as an RPC.

    For more information, see the "System Stored Procedures" section in the Books Online article "Creating a Stored Procedure."


Known issues

Here are some known issues that can prevent plan caching:
  • You use BLOB variables as a Stored Procedure parameter. Klicken Sie für Weitere Informationen auf die folgende KB-Artikelnummer:
    2380435FIX: The query plan for a stored procedure is not cached if the stored procedure uses a BLOB variable and the variable is used in a string function in Microsoft SQL Server 2008
  • You use OPEN SYMMETRIC KEY in a Stored Procedure/Query Batch. For more information, see the following MSDN blog entry:
    http://blogs.msdn.com/b/sqlserverfaq/Archive/2010/09/08/Open-Symmetric-Key-Command-prevents-Query-Plan-Caching.aspx

Eigenschaften

Artikel-ID: 263889 - Geändert am: Mittwoch, 24. November 2010 - Version: 1.0
Die Informationen in diesem Artikel beziehen sich auf:
  • Microsoft SQL Server 7.0 Standard Edition
  • Microsoft SQL Server 2000 Standard Edition
  • Microsoft SQL Server 2005 Standard Edition
  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL Server 2005 Enterprise Edition
  • Microsoft SQL Server 2005 Express Edition
  • Microsoft SQL Server 2005 Workgroup Edition
Keywords: 
kbinfo kbmt KB263889 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: 263889
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