Felsöka blockeringsproblem som orsakas av kompileringslås

Den här artikeln beskriver hur du felsöker och löser blockeringsproblem som orsakas av kompileringslås.

Ursprunglig produktversion: SQL Server
Ursprungligt KB-nummer: 263889

Sammanfattning

I Microsoft SQL Server finns det i allmänhet bara en kopia av en lagrad procedurplan i cacheminnet i taget. För att framtvinga detta krävs serialisering av vissa delar av kompileringsprocessen, och den här synkroniseringen utförs delvis med hjälp av kompileringslås. Om många anslutningar samtidigt kör samma lagrade procedur och ett kompileringslås måste hämtas för den lagrade proceduren varje gång den körs, kan sessions-ID :er (SPID) börja blockera varandra när de försöker få ett exklusivt kompileringslås på objektet.

Följande är några typiska egenskaper för kompileringsblockering som kan observeras i blockeringsutdata:

• waittype för de blockerade och (vanligtvis) blockerande sessions-SPID:erna är LCK_M_X (exklusivt) och waitresource är av formatet OBJECT: dbid: object_id [[COMPILE]], där object_id är objekt-ID för den lagrade proceduren.

• Blockerare har waittype NULL, status kan köras. Blockerade sessioner har waittypeLCK_M_X (exklusivt lås), status i viloläge.

• Den övergripande varaktigheten för blockeringsincidenten kan vara lång, men det finns ingen enskild session (SPID) som blockerar de andra SPID:erna under en längre tid. Det finns rullande blockering; Så snart en kompilering är klar tar en annan SPID över rollen som huvudblockerare i flera sekunder eller mindre, och så vidare.

Följande information kommer från en ögonblicksbild av sys.dm_exec_requests under den här typen av blockering:

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]]

waitresource I kolumnen (6:834102) är 6 databas-ID:t och 834102 är objekt-ID. Det här objekt-ID:t tillhör en lagrad procedur, inte till en tabell.

Scenarier som leder till kompilering av lås

I följande scenarier beskrivs orsakerna till exklusiva kompileringslås för lagrade procedurer eller utlösare.

Lagrad procedur körs utan fullständigt kvalificerat namn

• Användaren som kör den lagrade proceduren är inte ägare till proceduren.
• Namnet på den lagrade proceduren är inte fullständigt kvalificerat med objektägarens namn.

Om användaren dbo till exempel äger objektet dbo.mystoredproc och en annan användare , Harrykör den här lagrade proceduren med kommandot exec mystoredprocmisslyckas den första cachesökningen efter objektnamn eftersom objektet inte är ägarkvalificerat. (Det är ännu inte känt om det finns någon annan lagrad procedur med namnet Harry.mystoredproc . Därför kan SQL Server inte vara säker på att den cachelagrade planen för dbo.mystoredproc är den rätta att köra.) SQL Server hämtar sedan ett exklusivt kompileringslås på proceduren och förbereder sig för att kompilera proceduren. Detta inkluderar att matcha objektnamnet till ett objekt-ID. Innan SQL Server kompilerar planen använder SQL Server det här objekt-ID:t för att utföra en mer exakt sökning av procedurens cacheminne och kan hitta en tidigare kompilerad plan även utan ägarkvalifikation.

Om en befintlig plan hittas återanvänder SQL Server den cachelagrade planen och kompilerar inte den lagrade proceduren. Bristen på ägarkvalificering tvingar dock SQL Server att utföra en andra cachesökning och få ett exklusivt kompileringslås innan programmet fastställer att den befintliga cachelagrade körningsplanen kan återanvändas. Om du hämtar låset och utför sökningar och annat arbete som behövs för att nå den här punkten kan det medföra en fördröjning för kompileringslåsen som leder till blockering. Detta gäller särskilt om många användare som inte äger den lagrade proceduren samtidigt kör proceduren utan att ange ägarens namn. Även om du inte ser SPID:er som väntar på kompileringslås kan bristen på ägarkvalificering medföra fördröjningar i körningen av lagrade procedurer och orsaka hög CPU-användning.

Följande händelsesekvens registreras i en SQL Server Extended Event-session när det här problemet uppstår.

sp_cache_miss inträffar när cachesökningen efter namn misslyckas, men sedan hittades en matchande cachelagrad plan i cacheminnet efter att det tvetydiga objektnamnet löstes till ett objekt-ID och det finns en sp_cache_hit händelse.

Lösningen på det här problemet med kompileringslåsning är att se till att referenser till lagrade procedurer är ägarkvalificerade. (I stället för exec mystoredprocanvänder du exec dbo.mystoredproc.) Även om ägarkvalificering är viktigt av prestandaskäl behöver du inte kvalificera den lagrade processen med databasnamnet för att förhindra extra cache-sökning.

Blockering som orsakas av kompileringslås kan identifieras med hjälp av vanliga felsökningsmetoder för blockering.

Lagrad procedur omkompileras ofta

Omkompilering är en förklaring till kompileringslås på en lagrad procedur eller utlösare. Sätt att få en lagrad procedur att kompilera om inkluderar EXECUTE... WITH RECOMPILE, CREATE PROCEDURE ...WITH RECOMPILEeller använder sp_recompile. Mer information finns i Kompilera om en lagrad procedur. Lösningen i det här fallet är att minska eller eliminera omkompileringen.

Lagrad procedur är prefix med sp_**

Om namnet på den lagrade proceduren börjar med sp_ prefixet och det inte finns i huvuddatabasen ser du sp_cache_miss innan cacheträffen för varje körning, även om du äger den lagrade proceduren. Det beror på att prefixet sp_ talar om för SQL Server att den lagrade proceduren är en system lagrad procedur och att system lagrade procedurer har olika regler för namnmatchning. (Den önskade platsen finns i huvuddatabasen.) Namnen på användarskapade lagrade procedurer bör inte börja med sp_.

Lagrad procedur anropas med ett annat skiftläge (övre/nedre)

Om en ägarkvalificerad procedur körs med ett annat bokstavsfall (övre eller nedre) från det fall som användes för att skapa den, kan proceduren utlösa en CacheMiss-händelse eller begära ett COMPILE-lås. För att illustrera, observera det olika bokstavsfallet som används i CREATE PROCEDURE dbo.SalesData ... jämfört med EXEC dbo.salesdata. Så småningom använder proceduren den cachelagrade planen och är inte omkompilerad. Men begäran om ett COMPILE-lås kan ibland orsaka en blockeringskedja som beskrevs tidigare. Blockeringskedjan kan inträffa om det finns många sessioner (SPID) som försöker utföra samma procedur med hjälp av ett annat ärende än det fall som användes för att skapa den. Detta gäller oavsett vilken sorteringsordning eller sortering som används på servern eller i databasen. Orsaken till det här beteendet är att algoritmen som används för att hitta proceduren i cachen baseras på hashvärden (för prestanda) och att hash-värdena kan ändras om fallet är annorlunda.

Lösningen är att släppa och skapa proceduren med samma bokstavsfall som det som används när programmet kör proceduren. Du kan också se till att proceduren körs från alla program med rätt skiftläge (övre eller lägre).

Lagrad procedur anropas som en språkhändelse

Om du försöker köra en lagrad procedur som en språkhändelse i stället för som en RPC måste SQL Server parsa och kompilera språkhändelsefrågan, fastställa att frågan försöker köra den specifika proceduren och sedan försöka hitta en plan i cacheminnet för den proceduren. För att undvika den här situationen där SQL Server måste parsa och kompilera språkhändelsen kontrollerar du att frågan skickas till SQL Server som en RPC. I .NET-kod kan du till exempel använda SqlCommand.CommandType.StoredProcedure för att säkerställa en RPC-händelse.

Lagrad procedur eller sp_executesql använder en strängparameter som är större än 8 KB

Om du anropar en lagrad procedur eller sp_executesql och skickar en strängparameter som är större än 8 KB använder SQL Server en blob-datatyp (binary large object) för att lagra parametern. Därför sparas inte frågeplanen för den här körningen i plancachen. Därför måste varje körning av den lagrade proceduren eller sp_executesql hämta ett kompileringslås för att kompilera en ny plan. Den här planen ignoreras när körningen är klar. Mer information finns i anteckningen i Cachelagring och återanvändning av körningsplan för strängliteraler som är större än 8 kB. Undvik kompileringslåset i det här scenariot genom att minska storleken på parametern till mindre än 8 kB.

Referenser

OPEN SYMMETRIC KEY-kommandot förhindrar cachelagring av frågeplan