Řešení potíží s blokováním způsobeným zámky kompilace

Platí pro: Microsoft SQL Server 2005 Standard EditionMicrosoft SQL Server 2005 Developer EditionMicrosoft SQL Server 2005 Enterprise Edition

Shrnutí


Na serveru Microsoft SQL Server je v mezipaměti vždy pouze jedna kopie uloženého plánu procedur. Vynucení toho vyžaduje serializaci některých částí procesu kompilace a tato synchronizace je částečně provedena pomocí zámků kompilace. Pokud mnoho připojení současně spouští stejnou uloženou proceduru a pro tuto uloženou proceduru je nutné získat zámek kompilace při každém spuštění, může se stát, že ID relace (SPIDs) se navzájem zablokuje, protože se každý pokusí získat exkluzivní kompilační zámek na Objekt.

Následují některé typické charakteristiky blokování kompilace, které lze pozorovat v blokováním výstupu:

  • waittype pro blokující a (obvykle) identifikátory SPID relace je LCK_M_X (výhradní) a waitresource je ve tvaru Object: dbid:object_id [[kompilovat]], "kde" object_id "je ID objektu uložené procedury.
  • Blokant má hodnotu waittype null, nelze jej spustit. Blockees mít typ waittype LCK_M_X (výhradní zámek), stav spánku.
  • I když délka blokující události může být dlouhá, neexistuje žádné číslo SPID, které blokuje ostatní SPID dlouhou dobu. Je tam zablokování. Jakmile je jedna kompilace kompletní, převezme další SPID roli blokování hlavy na několik sekund nebo méně atd.

Následující informace jsou ze snímku Sys. dm_exec_requests během tohoto typu blokování:

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

Ve sloupci waitresource ("6:834102"), 6 je ID databáze a 834102 je ID objektu. Uvědomte si prosím, že ID objektu náleží k uložené proceduře, nikoli k tabulce.

Další informace


Rekompilace uložených procedur je jedním z vysvětlení pro kompilaci zámků v uložené proceduře nebo aktivační události. Řešením je v tomto případě snížení nebo odstranění překompilování. Vysvětlení nejobvyklejší příčiny, proč může být nutné překompilovat uloženou proceduru, a některé užitečné informace o snížení četnosti překompilování naleznete v následujícím článku znalostní báze Microsoft Knowledge Base:

243586 řešení potíží s překompilováním uložených procedur

Další scénáře, které vedou ke kompilování zámků:

  1. Uložená procedura je spuštěna bez úplného názvu
    • Uživatel, který spouští uloženou proceduru, není vlastníkem procedury.
    • Název uložené procedury není plně kvalifikován jménem vlastníka objektu.

    Pokud například uživatel "dbo" vlastní objekt dbo. mystoredproc a jiný uživatel, "Harry," spustí tuto uloženou proceduru pomocí příkazu "EXEC mystoredproc", počáteční vyhledávání mezipaměti podle názvu objektu se nezdaří, protože objekt není kvalifikovaný pro vlastníka. (Ještě není známo, zda existuje jiná uložená procedura s názvem Harry. mystoredproc existuje. Proto SQL Server nemůže být přesvědčen, že plán pro dbo. mystoredproc je správný pro vykonání.) SQL Server poté získá exkluzivní kompilovací zámek procedury a provede přípravu na kompilaci procedury. To zahrnuje překlad názvu objektu na ID objektu. Předtím, než SQL Server zkompiluje plán, SQL Server použije toto ID objektu k přesnějším prohledávání mezipaměti procedur a může vyhledat dříve zkompilovaný plán i bez kvalifikace vlastníka. Pokud je nalezen existující plán, server SQL znovu použije plán uložený v mezipaměti a ve skutečnosti nesestaví uloženou proceduru. Avšak absence vlastnického postavení nutí SQL Server provádět druhé vyhledávání v mezipaměti a získat exkluzivní kompilovací zámek, než program určí, že existující plán vykonání v mezipaměti lze znovu použít. Získání zámku a provádění vyhledávání a další práce potřebné k dosažení tohoto bodu mohou způsobit zpoždění zámků kompilace, které vedou k zablokování. To platí zejména v případě, že mnoho uživatelů, kteří nejsou vlastníkem uložené procedury, souběžně spouští proceduru bez zadání jména vlastníka. Mějte na zřeteli, že i když nevidíte SPID čekající na zámky kompilace, nedostatek vlastnického kvalifikace může způsobit zpoždění při provádění uložené procedury a zbytečně vysoké využití procesoru. V případě výskytu tohoto problému bude do relace rozšířené události serveru SQL Server zaznamenána následující posloupnost událostí.

    Název události Text
    rpc_starting mystoredproc
    sp_cache_miss mystoredproc
    sql_batch_starting mystoredproc
    sp_cache_hit mystoredproc
    ... ...

    sp_cache_miss nastává v případě, že se nezdaří vyhledávání v mezipaměti podle názvu, ale poté byl v mezipaměti nalezen odpovídající plán s mezipamětí poté, co byl název objektu nejasos přeložen na ID objektu a došlo k události sp_cache_hit . Řešením tohoto problému uzamknutí kompilace je zajistit, aby odkazy na uložené procedury byly kvalifikované pro vlastníky. (Místo exec mystoredprocpoužijte exec dbo. mystoredproc.) I když je kvalifikace vlastníka důležitá z důvodu výkonu, není nutné kvalifikovat uloženou funkci s názvem databáze, aby se zabránilo vyhledání další mezipaměti. Blokování, které je způsobeno zámky kompilace, lze zjistit pomocí standardních blokováním metod odstraňování potíží.

  2. Uložená procedura má předponou "sp_"

    Pokud název uložené procedury začíná předponou "sp_" a není v hlavní databázi, zobrazí se sp_cache_miss před vstupem do mezipaměti pro každé spuštění, a to i v případě, že jste uloženou proceduru kvalifikovi vlastníkem. Důvodem je skutečnost, že sp_ předpona sděluje serveru SQL Server, že uložená procedura je systémová uložená procedura a systémové uložené procedury mají rozdílná pravidla pro překlad názvů. (Upřednostňované umístění je v hlavní databázi.) Názvy uložených procedur vytvořených uživatelem by neměly začínat na "sp_".

  3. Uložená procedura je vyvolána pomocí jiného případu (horní/dolní)

    Pokud je postup s kvalifikovaným vlastníkem prováděn pomocí jiného případu (horního nebo dolního) z případu, který byl použit k jeho vytvoření, může procedura spustit událost CacheMiss nebo požádat o zámek COMPILE. Procedura nakonec používá plán uložený v mezipaměti a není znovu zkompilována. Avšak žádost o zámek kompilace může někdy způsobit situaci blokující řetěz, pokud existuje mnoho identifikátorů SPID, které se pokoušejí provést stejný postup pomocí jiného případu, než je případ použitý k jeho vytvoření. To platí bez ohledu na pořadí řazení nebo řazení používané na serveru nebo v databázi. Důvodem tohoto chování je, že algoritmus použitý k nalezení procedury v mezipaměti je založen na hodnotách hash (pro výkon) a hodnoty hash se mohou změnit, pokud je případ odlišný. Řešením je vynechat a vytvořit proceduru pomocí stejného případu, jako je ten, který se používá při provádění procedury. Je také možné ověřit, zda je procedura spouštěna ze všech aplikací, a to pomocí správného případu (horní nebo dolní).

  4. Uložená procedura je vyvolána jako událost jazyka

    Pokud se pokusíte spustit uloženou proceduru jako událost jazyka namísto jako RPC, musí SQL Server analyzovat a kompilovat dotaz na jazyk, zjistit, zda se dotaz pokouší provést konkrétní proceduru, a pokusit se najít v mezipaměti plán pro tuto proceduru. Chcete-li předejít této situaci, kdy SQL Server musí analyzovat a kompilovat jazykovou událost, ujistěte se, že je dotaz odeslán do SQL jako RPC.

    Další informace naleznete v části systémové uložené procedury v článku příručky Books Online "vytvoření uložené procedury".

Známé problémy

Zde jsou některé známé problémy, které mohou zabránit ukládání plánu do mezipaměti:

  • Proměnné typu BLOB se používají jako parametr uložené procedury. Další informace získáte v následujícím článku znalostní báze Microsoft Knowledge Base:
    2380435 Oprava: plán dotazů pro uloženou proceduru není uložen do mezipaměti, pokud uložená procedura používá proměnnou BLOB a proměnná je použita v řetězcové funkci serveru Microsoft SQL Server 2008
  • V dávkové proceduře uložená procedura/dotaz můžete použít OPEN symetrický klíč. Další informace naleznete v následující položce blogu MSDN: