Blokkeringsproblemen oplossen die worden veroorzaakt door compilatie van vergrendeling

In dit artikel wordt beschreven hoe u blokkeringsproblemen kunt oplossen die worden veroorzaakt door compileervergrendelingen.

Oorspronkelijke productversie: SQL Server
Oorspronkelijk KB-nummer: 263889

Samenvatting

In Microsoft SQL Server bevindt zich slechts één exemplaar van een opgeslagen procedureplan in de cache tegelijk. Het afdwingen hiervan vereist serialisatie van sommige onderdelen van het compilatieproces. Deze synchronisatie wordt gedeeltelijk uitgevoerd met behulp van compileervergrendelingen. Als veel verbindingen gelijktijdig dezelfde opgeslagen procedure uitvoeren en een compileervergrendeling moet worden verkregen voor die opgeslagen procedure telkens wanneer deze wordt uitgevoerd, kunnen sessie-id's (SPID's) elkaar blokkeren omdat ze elk proberen een exclusieve compileervergrendeling op het object te verkrijgen.

Hier volgen enkele typische kenmerken van compileerblokkeringen die kunnen worden waargenomen in de blokkeringsuitvoer:

• waittype voor de geblokkeerde en (meestal) blokkerende sessie SPID's is LCK_M_X (exclusief) en waitresource is van het formulier OBJECT: dbid: object_id [[COMPILE]], waarbij object_id de object-id van de opgeslagen procedure.

• Blokkers hebben waittype NULL, status kan worden uitgevoerd. Geblokkeerde sessies hebben waittypeLCK_M_X (exclusief vergrendelen), statussen slapen.

• Hoewel de totale duur van het blokkerende incident lang kan zijn, is er geen enkele sessie (SPID) die de andere SPID's gedurende lange tijd blokkeert. Er is rollende blokkering; zodra de ene compilatie is voltooid, neemt een andere SPID de rol van hoofdblokkering enkele seconden of minder over, enzovoort.

De volgende informatie is afkomstig van een momentopname van sys.dm_exec_requests tijdens dit soort blokkeringen:

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 de waitresource kolom (6:834102) is 6 de database-id en 834102 de object-id. Deze object-id behoort tot een opgeslagen procedure, niet tot een tabel.

Scenario's die leiden tot het compileren van vergrendelingen

In de volgende scenario's worden de oorzaken beschreven voor exclusieve compileervergrendelingen voor opgeslagen procedures of triggers.

Opgeslagen procedure wordt uitgevoerd zonder volledig gekwalificeerde naam

• De gebruiker die de opgeslagen procedure uitvoert, is niet de eigenaar van de procedure.
• De naam van de opgeslagen procedure is niet volledig gekwalificeerd met de naam van de objecteigenaar.

Als gebruiker dbo bijvoorbeeld eigenaar is van een object dbo.mystoredproc en een andere gebruiker, Harryvoert u deze opgeslagen procedure uit met behulp van de opdracht exec mystoredproc, mislukt de eerste cachezoekactie op objectnaam omdat het object niet eigenaar-gekwalificeerd is. (Het is nog niet bekend of er een andere opgeslagen procedure met de naam Harry.mystoredproc bestaat. Daarom kan SQL Server er niet zeker van zijn dat het plan dbo.mystoredproc in de cache voor de juiste is om uit te voeren.) SQL Server verkrijgt vervolgens een exclusieve compileervergrendeling op de procedure en maakt voorbereidingen om de procedure te compileren. Dit omvat het omzetten van de objectnaam in een object-id. Voordat SQL Server het plan compileert, gebruikt SQL Server deze object-id om een nauwkeurigere zoekopdracht uit te voeren in de procedurecache en een eerder gecompileerd plan te vinden, zelfs zonder eigenaarskwalificatie.

Als er een bestaand plan wordt gevonden, wordt het plan in de cache opnieuw gebruikt en wordt de opgeslagen procedure niet daadwerkelijk gecompileerd. Door het ontbreken van eigenaarskwalificatie moet SQL Server echter een tweede cachezoekactie uitvoeren en een exclusieve compileervergrendeling verkrijgen voordat het programma bepaalt dat het bestaande uitvoeringsplan in de cache opnieuw kan worden gebruikt. Het verkrijgen van de vergrendeling en het uitvoeren van zoekacties en ander werk dat nodig is om dit punt te bereiken, kan leiden tot een vertraging voor de compilatievergrendelingen die tot blokkeren leiden. Dit geldt met name als veel gebruikers die niet de eigenaar van de opgeslagen procedure zijn, de procedure gelijktijdig uitvoeren zonder de naam van de eigenaar op te geven. Zelfs als u GEEN SPID's ziet die wachten op compileervergrendelingen, kan het ontbreken van eigenaarskwalificatie vertragingen veroorzaken bij het uitvoeren van opgeslagen procedures en een hoog CPU-gebruik veroorzaken.

De volgende reeks gebeurtenissen wordt vastgelegd in een uitgebreide SQL Server-gebeurtenissessie wanneer dit probleem optreedt.

sp_cache_miss treedt op wanneer het opzoeken van de cache op naam mislukt, maar er uiteindelijk een overeenkomend plan in de cache is gevonden nadat de dubbelzinnige objectnaam is omgezet in een object-id en er een sp_cache_hit gebeurtenis is.

De oplossing voor dit probleem bij het compileren van vergrendelingen is ervoor te zorgen dat verwijzingen naar opgeslagen procedures eigenaar-gekwalificeerd zijn. (Gebruik exec in plaats van exec mystoredprocdbo.mystoredproc.) Hoewel de kwalificatie van eigenaars belangrijk is om prestatieredenen, hoeft u de opgeslagen procedure niet in aanmerking te nemen met de databasenaam om te voorkomen dat er extra cacheopzoekacties worden uitgevoerd.

Blokkeren die wordt veroorzaakt door compileervergrendelingen, kunnen worden gedetecteerd met behulp van standaardmethoden voor het blokkeren van problemen.

Opgeslagen procedure wordt regelmatig opnieuw gecompileerd

Hercompilatie is één verklaring voor het compileren van vergrendelingen voor een opgeslagen procedure of trigger. Manieren om ervoor te zorgen dat een opgeslagen procedure opnieuw wordt gecompileerd, CREATE PROCEDURE ...WITH RECOMPILEof EXECUTE... WITH RECOMPILEgebruiktsp_recompile. Zie Een opgeslagen procedure opnieuw compileren voor meer informatie. De oplossing in dit geval is om de hercompilatie te verminderen of te elimineren.

Opgeslagen procedure wordt voorafgegaan door sp_**

Als de naam van de opgeslagen procedure begint met het sp_ voorvoegsel en deze zich niet in de hoofddatabase bevindt, ziet u sp_cache_miss voordat de cache voor elke uitvoering wordt bereikt, zelfs als u de opgeslagen procedure in aanmerking neemt. Dit komt doordat het voorvoegsel AAN SQL Server vertelt dat de opgeslagen procedure een door het sp_ systeem opgeslagen procedure is en door systeem opgeslagen procedures verschillende regels voor naamomzetting hebben. (De voorkeurslocatie bevindt zich in de hoofddatabase.) De namen van door de gebruiker gemaakte opgeslagen procedures mogen niet beginnen met sp_.

Opgeslagen procedure wordt aangeroepen met behulp van een ander hoofdlettergebruik (bovenste/onderste)

Als een door de eigenaar gekwalificeerde procedure wordt uitgevoerd met behulp van een andere letter (hoofdletter of lager) van de aanvraag die is gebruikt om deze te maken, kan de procedure een CacheMiss-gebeurtenis activeren of een COMPILE-vergrendeling aanvragen. Ter illustratie ziet u de verschillende letters die worden gebruikt in CREATE PROCEDURE dbo.SalesData ... versus EXEC dbo.salesdata. Uiteindelijk maakt de procedure gebruik van het plan in de cache en wordt het niet opnieuw gecompileerd. Maar de aanvraag voor een COMPILE-vergrendeling kan soms een blokkeringsketensituatie veroorzaken die eerder is beschreven. De blokkerende keten kan optreden als er veel sessies (SPID's) zijn die dezelfde procedure proberen uit te voeren met behulp van een andere case dan de case die is gebruikt om deze te maken. Dit geldt ongeacht de sorteervolgorde of sortering die wordt gebruikt op de server of in de database. De reden voor dit gedrag is dat het algoritme dat wordt gebruikt om de procedure in de cache te vinden, is gebaseerd op hash-waarden (voor prestaties) en de hash-waarden kunnen veranderen als het geval anders is.

De oplossing is om de procedure te verwijderen en te maken met behulp van dezelfde lettercase als degene die wordt gebruikt wanneer de toepassing de procedure uitvoert. U kunt er ook voor zorgen dat de procedure wordt uitgevoerd vanuit alle toepassingen met behulp van het juiste hoofdlettergebruik (hoofdletter of lager).

Opgeslagen procedure wordt aangeroepen als een taalgebeurtenis

Als u een opgeslagen procedure probeert uit te voeren als een taalevenement in plaats van als RPC, moet SQL Server de query voor taalevenementen parseren en compileren, bepalen of de query de specifieke procedure probeert uit te voeren en vervolgens een plan in de cache voor die procedure probeert te vinden. Om deze situatie te voorkomen waarbij SQL Server de taal gebeurtenis moet parseren en compileren, moet u ervoor zorgen dat de query als een RPC naar SQL Server wordt verzonden. In .NET-code kunt u bijvoorbeeld een RPC-gebeurtenis gebruiken SqlCommand.CommandType.StoredProcedure .

Opgeslagen procedure of sp_executesql maakt gebruik van een tekenreeksparameter die groter is dan 8 kB

Als u een opgeslagen procedure aanroept of sp_executesql en een tekenreeksparameter van meer dan 8 kB doorgeeft, gebruikt SQL Server een gegevenstype voor een binair groot object (BLOB) om de parameter op te slaan. Als gevolg hiervan wordt het queryplan voor deze uitvoering niet bewaard in de plancache. Daarom moet elke uitvoering van de opgeslagen procedure of sp_executesql een compileervergrendeling verkrijgen om een nieuw plan te compileren. Dit plan wordt verwijderd wanneer de uitvoering is voltooid. Zie de opmerking in de cache van het uitvoeringsplan en hergebruik met betrekking tot letterlijke tekenreeksen die groter zijn dan 8 kB voor meer informatie. Om de compileervergrendeling in dit scenario te voorkomen, verkleint u de grootte van de parameter tot minder dan 8 kB.

Verwijzingen

OPDRACHT OPEN SYMMETRIC KEY voorkomt cacheopslag van queryplannen