Analýza a zabránění zablokování ve službě Azure SQL Database a databázi SQL Fabric

Platí pro:Azure SQL DatabaseSQL databází ve Fabric

V tomto článku se dozvíte, jak identifikovat vzájemné zablokování, pomocí grafů vzájemného zablokování a úložiště dotazů identifikovat dotazy ve vzájemném zablokování a plánovat a testovat změny, aby se zabránilo opakovanému zablokování. Tento článek se týká Azure SQL Database a Fabric SQL databáze, která sdílí mnoho funkcí s Azure SQL Database.

Tento článek se zaměřuje na identifikaci a analýzu zablokování kvůli kolizí zámků. Přečtěte si více o jiných typech zablokování v prostředcích, které mohou způsobit zablokování.

Jak dochází k zablokování

Každá nová databáze ve službě Azure SQL Database má ve výchozím nastavení povolenou databázovou možnost izolace snímků při potvrzeném čtení (RCSI). Blokování mezi relacemi čtení a zápisu dat se minimalizuje v rámci RCSI, které ke zvýšení souběžnosti využívá verzování řádků. Blokování a vzájemné zablokování se mohou v databázích ve službě Azure SQL Database stále vyskytovat, protože:

• Dotazy, které upravují data, by mohly vzájemně blokovat.

• Dotazy můžou běžet na úrovních izolace, které zvyšují blokování. Úrovně izolace je možné zadat pomocí metod klientské knihovny, nápovědy k dotazůmnebo SET TRANSACTION ISOLATION LEVEL v transact-SQL.

• RCSI může být zakázána, což způsobí, že databáze používá sdílené zámky (S) k ochraně příkazů SELECT spuštěných pod úrovní potvrzené izolace čtení. To by mohlo zvýšit blokování a uvíznutí.

Příklad vzájemného zablokování

Zablokování nastane, když dva nebo více úkolů se navzájem trvale blokují, protože každý úkol má zámek na zdroji, který se ostatní úkoly snaží uzamknout. Vzájemné zablokování se také nazývá cyklická závislost: v případě dvouúlohového vzájemného zablokování má transakce A závislost na transakci B a transakce B zavře kruh tím, že má závislost na transakci A.

Příklad:

1. Relace A zahájí explicitní transakci a spustí příkaz aktualizace, který získá zámek aktualizace (U) na jednom řádku tabulky SalesLT.Product, který je převeden na výhradní zámek (X).

2. Session B spustí příkaz aktualizace, který upraví tabulku SalesLT.ProductDescription. Příkaz update se spojí s SalesLT.Product tabulkou a najde správné řádky, které se mají aktualizovat.

   • Relace B získá zámek aktualizace (U) na 72 řádcích v tabulce SalesLT.ProductDescription.

   • Sezení B potřebuje sdílený zámek na řádcích v tabulce SalesLT.Product, včetně řádku, který je zamčený sezením A. Sezení B je zablokované na SalesLT.Product.

3. Relace A pokračuje ve své transakci a nyní spustí aktualizaci tabulky SalesLT.ProductDescription . Relace A je blokována relací B na SalesLT.ProductDescription.

Všechny transakce v zablokování čekají neomezeně dlouho, pokud není například jedna ze zúčastněných transakcí vrácena zpět, například kvůli ukončení její relace.

Monitor zablokování databázového enginu pravidelně ověřuje úlohy ve stavu zablokování. Pokud monitor vzájemného zablokování zjistí cyklickou závislost, zvolí jeden z úkolů jako oběť a ukončí jeho transakci s chybou 1205: Transaction (Process ID <N>) was deadlocked on lock resources with another process and is chosen as the deadlock victim. Rerun the transaction. Přerušením vzájemného zablokování tímto způsobem umožňuje ostatním úkolům ve vzájemném zablokování dokončit své transakce.

Poznámka:

Další informace o kritériích pro volbu oběti vzájemného zablokování najdete v části se seznamem procesů vzájemného zablokování v tomto článku.

Aplikace s transakcí zvolenou jako oběť vzájemného zablokování by měla opakovat transakci, která se obvykle dokončí po dokončení ostatních transakcí zapojených do zablokování.

Osvědčeným postupem je zavést krátké náhodné zpoždění před dalším pokusem opětovného provedení, abyste se vyhnuli stejné překážce. Přečtěte si další informace o tom, jak navrhnout logiku opakování pro přechodné chyby.

Výchozí úroveň izolace ve službě Azure SQL Database

Nové databáze v Azure SQL Database mají ve výchozím nastavení povolený režim čtení potvrzených snímků (RCSI). RCSI změní chování potvrzené úrovně izolace čtení tak, aby používala verzování řádků k zajištění konzistence na úrovni příkazů bez použití sdílených (S) zámků pro SELECT.

S povolenou konfigurací RCSI:

• Příkazy, které čtou data, neblokují příkazy, které upravují data.
• Příkazy, které upravují data, neblokují příkazy čtení dat.

Úroveň izolace snímků je ve výchozím nastavení povolená také pro nové databáze ve službě Azure SQL Database. Izolace snímku je další úroveň izolace založená na řádcích, která poskytuje konzistenci na úrovni transakcí pro data a která používá verze řádků k výběru řádků pro aktualizaci. Chcete-li použít izolaci snímků, musí dotazy nebo připojení explicitně nastavit úroveň izolace transakcí na SNAPSHOT. To lze provést pouze v případě, že je pro databázi povolená izolace snímků.

Pomocí jazyka Transact-SQL můžete zjistit, jestli je povolená izolace RCSI nebo snímku. Připojte se k databázi ve službě Azure SQL Database a spusťte následující dotaz:

SELECT name,
       is_read_committed_snapshot_on,
       snapshot_isolation_state_desc
FROM sys.databases
WHERE name = DB_NAME();
GO

Pokud je povolená analýza RCSI, vrátí sloupec is_read_committed_snapshot_on hodnotu 1. Pokud je povolena izolace snímku, vrátí sloupec snapshot_isolation_state_desc hodnotu ON.

Pokud je RCSI zakázáno pro databázi ve službě Azure SQL Database, prozkoumejte, proč bylo RCSI zakázáno, před jeho opětovným povolením. Kód aplikace může očekávat, že dotazy, které čtou data, budou blokovány dotazy zápisu dat, což vede k nesprávným výsledkům ze závodních podmínek, když je RCSI povoleno.

Interpretujte události vzájemného zablokování

Událost deadlocku se vygeneruje poté, co správce deadlocků ve službě Azure SQL Database zjistí deadlock a označí transakci jako oběť. Jinými slovy, pokud nastavíte upozornění na zablokování, oznámení se aktivuje po vyřešení jednotlivého zablokování. Pro toto zablokování není potřeba provést žádnou akci uživatele. Aplikace by měly být zapsány tak, aby zahrnovaly logiku opakování , aby automaticky pokračovaly po zobrazení chyby 1205: Transaction (Process ID <N>) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Je ale užitečné nastavit upozornění, protože zablokování se může opakovat. Upozornění na vzájemné zablokování vám umožňují zjistit, zda ve vaší databázi probíhá vzorec opakovaných zablokování, a v takovém případě byste se mohli rozhodnout zasáhnout a zabránit tak dalšímu vzájemnému zablokování. Další informace o upozorňování najdete v části Monitorování a upozornění na zablokování v tomto článku.

Hlavní metody, které brání zablokování

Nejnižším rizikovým přístupem, který brání opětovnému zablokování, je obecně ladění neclusterovaných indexů za účelem optimalizace dotazů zapojených do zablokování.

• Riziko je pro tento přístup nízké, protože ladění neclusterovaných indexů nevyžaduje změny samotného kódu dotazu, což snižuje riziko chyby uživatele při přepsání jazyka Transact-SQL, které způsobí vrácení nesprávných dat uživateli.

• Efektivní ladění neklastrovaného indexu pomáhá dotazům najít data, které lze efektivněji číst a upravovat. Snížením množství dat, ke kterým dotaz potřebuje přístup, se snižuje pravděpodobnost blokování a často lze předejít zablokování.

V některých případech může vytvoření nebo ladění clusterovaného indexu snížit blokování a záseky. Vzhledem k tomu, že clusterovaný index je součástí všech neclusterovaných definic indexů, může být vytvoření nebo úprava clusterovaného indexu náročná na vstupně-výstupní operace a časově náročná operace u větších tabulek s existujícími neclusterovanými indexy. Přečtěte si další informace o pokynech pro návrh clusterovaného indexu.

Pokud ladění indexu není úspěšné, aby se zabránilo zablokování, jsou k dispozici další metody:

• Pokud k vzájemnému zablokování dojde pouze tehdy, když je pro některý z dotazů zapojených do zablokování zvolen určitý plán, pak vynucení plánu dotazu pomocí úložiště dotazů může zabránit opakovanému zablokování.

• Přepsání transact-SQL pro jednu nebo více transakcí zapojených do zablokování může také pomoct zabránit zablokování. Rozdělení explicitních transakcí do menších transakcí vyžaduje pečlivé kódování a testování, aby se zajistila platnost dat při souběžných úpravách.

Další informace o každém z těchto přístupů najdete v části Zabránění opakovanému zablokování tohoto článku.

Monitorování a upozorňování na zablokování

V tomto článku používáme ukázkovou databázi AdventureWorksLT k nastavení výstrah pro zablokování, vyvolání příkladového zablokování, analýze grafu zablokování tohoto příkladu a testování změn, které zabrání opětovnému výskytu zablokování.

V tomto článku používáme klienta SQL Server Management Studio (SSMS), protože obsahuje funkci pro zobrazení grafů zablokování v interaktivním vizuálním režimu. Pomocí jiných klientů, jako je Azure Data Studio, můžete postupovat podle příkladů, ale grafy zablokování můžete zobrazit jenom jako XML.

Vytvoření databáze AdventureWorksLT

Pokud chcete postupovat podle příkladů, vytvořte novou databázi ve službě Azure SQL Database a jako zdroj dat vyberte Ukázková data.

Podrobné pokyny k vytvoření AdventureWorksLT pomocí Azure portálu, Azure CLI nebo PowerShellu najdete tak, že v části Rychlý start vyberete přístup ve Vytvoření jedné databáze Azure SQL Database.

Nastavte upozornění na zablokování v portálu Azure

Pokud chcete nastavit upozornění na události zablokování, postupujte podle pokynů v článku Vytvoření upozornění pro Azure SQL Database a Azure Synapse Analytics pomocí webu Azure Portal.

Jako název signálu pro výstrahu vyberte Deadlock. Nakonfigurujte skupinu akcí tak, aby vás upozornila pomocí zvolené metody, například typem akce E-mail/SMS/Nabízení/Hlas.

Shromažďování grafů vzájemného zablokování ve službě Azure SQL Database pomocí Extended Events

Grafy vzájemného zablokování jsou bohatým zdrojem informací týkajících se procesů a zámků, které jsou součástí zablokování. Pokud chcete shromažďovat grafy vzájemného zablokování pomocí rozšířených událostí (XEvents) ve službě Azure SQL Database, zachyťte událost sqlserver.database_xml_deadlock_report.

Grafy vzájemného zablokování s XEvents můžete shromažďovat buď pomocí cíle vyrovnávací paměti okruhu, nebo cíle souboru události. Důležité informace o výběru vhodného cílového typu jsou shrnuté v následující tabulce:

Přístup	Zaměstnanecké výhody	Úvahy	Scénáře použití
Cíl kruhové vyrovnávací paměti	- Jednoduché nastavení pouze s Transact-SQL.	– Data událostí se smažou, když se relace XEvents z jakéhokoli důvodu zastaví, například pokud je databáze přepnuta do offline režimu nebo dojde k automatickému převedení při selhání databáze. – Databázové prostředky se používají k údržbě dat ve kruhové vyrovnávací paměti a k dotazování dat relace.	– Shromážděte ukázková data trasování pro testování a učení. - Pokud nemůžete okamžitě nastavit relaci pomocí cílového souboru událostí, vytvořte řešení pro krátkodobé potřeby. – Použijte jako přistávací plochu pro data trasování, když máte nastavený automatizovaný proces pro uložení trasovacích dat do tabulky.
Cílový soubor události	– Ukládá data událostí do objektu blob ve službě Azure Storage, takže data jsou k dispozici i po zastavení relace. – Soubory událostí je možné stáhnout z webu Azure Portal nebo Průzkumníka služby Azure Storage a analyzovat místně, což nevyžaduje dotazování dat relací pomocí databázových prostředků.	– Nastavení je složitější a vyžaduje konfiguraci kontejneru Azure Storage a přihlašovacích údajů s oborem pro databázi.	– Obecné použití, pokud chcete, aby data událostí byla zachována i po ukončení relace události. – Chcete spustit trasování, které generuje větší objem dat událostí, než byste chtěli uchovávat v paměti.

Vyberte typ cíle, který chcete použít:

Cíl kruhového bufferu

Cíl vyrovnávací paměti okruhu je pohodlný a snadno nastavený, ale má omezenou kapacitu, což může způsobit ztrátu starších událostí. Kruhový buffer neukládá události trvale do úložiště a cílový kruhový buffer se vymaže, když je zastavena relace XEvents. To znamená, že události XEvent shromážděné nejsou k dispozici, když se databázový engine z jakéhokoli důvodu restartuje, například při přepnutí při selhání. Cíl kruhového bufferu je nejvhodnější pro učení a krátkodobé potřeby, pokud nemáte okamžitě možnost nastavit relaci XEvents k cílovému souboru událostí.

Tento ukázkový kód vytvoří relaci XEvents, která zachycuje grafy zablokování v paměti pomocí cíle prstencového vyrovnávacího bufferu. Maximální povolená paměť pro cíl kruhového bufferu je 4 MB a relace se automaticky spustí, když se databáze stane online, například při převzetí činnosti při selhání.

Pokud chcete vytvořit a poté spustit relaci XEvents pro událost sqlserver.database_xml_deadlock_report, která zapisuje do cíle kruhového bufferu, připojte se k databázi a spusťte následující Transact-SQL:

CREATE EVENT SESSION [deadlocks] ON DATABASE
ADD EVENT sqlserver.database_xml_deadlock_report
ADD TARGET package0.ring_buffer
WITH
(
    STARTUP_STATE = ON,
    MAX_MEMORY = 4 MB
);
GO

ALTER EVENT SESSION [deadlocks] ON DATABASE
STATE = START;
GO

Cíl souboru události

Cíl souboru události zachovává grafy zablokování u souborů, aby byly dostupné i po zastavení relace XEvents. Cílový soubor události také umožňuje zachytit více grafů zablokování bez přidělení další paměti pro kruhovou vyrovnávací paměť. Cíl souboru událostí je vhodný pro dlouhodobé použití a pro shromažďování větších objemů dat trasování.

Chcete-li vytvořit relaci XEvents, která zapisuje do cíle souboru události, postupujte následovně:

1. Nakonfigurujte kontejner Azure Storage pro uložení trasovacích souborů pomocí webu Azure Portal.
2. Vytvořte přihlašovací údaje s vymezeným oborem databáze pomocí jazyka Transact-SQL.
3. Vytvořte relaci XEvents pomocí jazyka Transact-SQL.

Konfigurace kontejneru Azure Storage

Pokud chcete nakonfigurovat kontejner Azure Storage, nejprve vytvořte nebo vyberte existující účet Azure Storage a pak kontejner vytvořte. Vygenerujte token sdíleného přístupového podpisu (SAS) pro kontejner. Tato část popisuje dokončení tohoto procesu na webu Azure Portal.

Poznámka:

Pokud chcete vytvořit a nakonfigurovat kontejner objektů blob služby Azure Storage pomocí PowerShellu, podívejte se na cílový kód souboru událostí pro rozšířené události ve službě Azure SQL Database. Alternativně může být vhodné použít Průzkumníka služby Azure Storage k vytvoření a konfiguraci kontejneru objektů blob služby Azure Storage místo webu Azure Portal.

Vytvoření nebo výběr účtu Azure Storage

K hostování kontejneru pro trasovací soubory můžete použít existující účet Azure Storage nebo vytvořit nový účet Azure Storage.

Použití existujícího účtu Azure Storage:

1. Na webu Azure Portal přejděte do skupiny prostředků, se kterou chcete pracovat.
2. V podokně Přehled v části Prostředkynastavte rozevírací seznam Typ na účet úložiště.
3. Vyberte účet úložiště, který chcete použít.

Pokud chcete vytvořit nový účet Azure Storage, postupujte podle kroků v části Vytvoření účtu úložiště Azure. Dokončete proces výběrem možnosti Přejít k prostředku v posledním kroku.

Vytvoření kontejneru

Na stránce účtu úložiště na webu Azure Portal:

1. V části Úložiště dat vyberte Kontejnery.
2. Vyberte + Kontejner a vytvořte nový kontejner. Zobrazí se podokno Nový kontejner.
3. Do pole Název zadejte název kontejneru.
4. Vyberte Vytvořit.
5. Po vytvoření vyberte kontejner ze seznamu.

Vytvoření sdíleného přístupového tokenu

Na stránce kontejneru na webu Azure Portal:

1. V části Nastavení vyberte Sdílené přístupové tokeny.

2. Nechte přepínač Způsob podepisování nastavený na výchozí výběr, Klíč k účtu.

3. V rozevíracím seznamu oprávnění vyberte oprávnění čtení, zápisua seznamu.

4. Nastavte Začátek na datum a čas, ve který chcete mít možnost zapisovat sledovací soubory. Volitelně můžete nakonfigurovat časové pásmo v rozevíracím seznamu níže Spustit.

5. Nastavte vypršení platnosti na datum a čas, kdy chcete, aby platnost těchto oprávnění vypršela. Volitelně můžete v rozevíracím seznamu níže nakonfigurovat časové pásmo u vypršení platnosti. Pokud chcete, můžete to nastavit na datum daleko v budoucnosti, například 10 let.

6. Vyberte Vygenerovat token a URL SAS. Na obrazovce se zobrazí token SAS blobu a adresa URL SAS blobu.

7. Zkopírujte a zachovejte hodnoty tokenu SAS objektu blob a adresy URL SAS objektu blob pro použití v dalších krocích.

Vytvořit přihlašovací údaje specifické pro databázi

Připojte se ke své databázi ve službě Azure SQL Database pomocí aplikace SSMS a spusťte následující kroky.

Pokud chcete vytvořit přihlašovací údaje s vymezeným oborem databáze, musíte nejprve v databázi vytvořit hlavní klíč databáze (DMK), pokud neexistuje.

Pokud DMK neexistuje, spusťte následující Transact-SQL k jeho vytvoření.

IF 0 = (SELECT COUNT(*)
        FROM sys.symmetric_keys
        WHERE symmetric_key_id = 101
              AND name = N'##MS_DatabaseMasterKey##')
    BEGIN
        PRINT N'Creating master key';
        CREATE MASTER KEY;
    END
ELSE
    BEGIN
        PRINT N'Master key already exists, no action taken';
    END
GO

Dále vytvořte přihlašovací údaje s vymezeným oborem databáze s následujícím jazykem Transact-SQL. Před spuštěním kódu:

• Upravte adresu URL tak, aby odrážela název vašeho účtu úložiště a název kontejneru. Tato adresa URL se nachází na začátku adresy SAS URL objektu blob , kterou jste zkopírovali při vytváření sdíleného přístupového tokenu. Text potřebujete jenom před prvním ? v řetězci.

• Upravte SECRET tak, aby obsahoval hodnotu tokenu SAS objektu Blob, kterou jste zkopírovali při vytváření sdíleného přístupového tokenu.

CREATE DATABASE SCOPED CREDENTIAL [https://yourstorageaccountname.blob.core.windows.net/yourcontainername]
    WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
    SECRET = 'sp=r&st=2022-04-08T14:34:21Z&se=2032-04-08T22:34:21Z&sv=2020-08-04&sr=c&sig=<sig>';
GO

Vytvořte relaci XEvents

Vytvořte a spusťte relaci XEvents s následujícím jazykem Transact-SQL. Před spuštěním příkazu:

• Nahraďte hodnotu tak filename , aby odrážela název vašeho účtu úložiště a název kontejneru. Tato adresa URL se nachází na začátku adresy SAS URL objektu blob , kterou jste zkopírovali při vytváření sdíleného přístupového tokenu. Text potřebujete jenom před prvním ? v řetězci.

• Volitelně můžete změnit uložený název souboru. Zadaný název souboru bude součástí skutečných názvů souborů používaných pro objekty blob, které ukládají data události: připojují se další hodnoty, aby všechny soubory událostí měly jedinečný název.

• Volitelně můžete do relace přidat další události.

CREATE EVENT SESSION [deadlocks_eventfile] ON DATABASE
ADD EVENT sqlserver.database_xml_deadlock_report
ADD TARGET package0.event_file
(
    SET filename = 'https://yourstorageaccountname.blob.core.windows.net/yourcontainername/deadlocks.xel'
)
WITH
(
    STARTUP_STATE = ON,
    MAX_MEMORY = 4 MB
);
GO

ALTER EVENT SESSION [deadlocks_eventfile] ON DATABASE
STATE = START;
GO

Vyvolat zablokování v AdventureWorksLT

Poznámka:

Tento příklad pracuje v databázi AdventureWorksLT s výchozím schématem a daty, když je povolen RCSI. Pokyny k vytvoření databáze najdete v tématu Vytvoření databáze AdventureWorksLT.

Pokud chcete způsobit zablokování, musíte k databázi AdventureWorksLT připojit dvě relace. Tyto sezení se označují jako Sezení A a Sezení B.

V Session A spusťte následující Transact-SQL. Tento kód zahájí explicitní transakci a spustí jeden příkaz, který aktualizuje SalesLT.Product tabulku. Za tímto účelem transakce získá zámek aktualizace (U) na jednom řádku tabulky SalesLT.Product, který se poté převede na výhradní zámek (X). Transakci ponecháme otevřenou.

BEGIN TRANSACTION;

UPDATE SalesLT.Product
SET SellEndDate = SellEndDate + 1
WHERE Color = 'Red';

Teď v relaci B spusťte následující transact-SQL. Tento kód explicitně nezačne transakci. Místo toho funguje v režimu automatické transakce. Tento příkaz aktualizuje SalesLT.ProductDescription tabulku. Aktualizace vyřadí zámek aktualizace (U) na 72 řádcích v SalesLT.ProductDescription tabulce. Dotaz se spojí s jinými tabulkami, včetně SalesLT.Product tabulky.

UPDATE SalesLT.ProductDescription
    SET Description = Description
FROM SalesLT.ProductDescription AS pd
     INNER JOIN SalesLT.ProductModelProductDescription AS pmpd
         ON pd.ProductDescriptionID = pmpd.ProductDescriptionID
     INNER JOIN SalesLT.ProductModel AS pm
         ON pmpd.ProductModelID = pm.ProductModelID
     INNER JOIN SalesLT.Product AS p
         ON pm.ProductModelID = p.ProductModelID
WHERE p.Color = 'Silver';

K dokončení této aktualizace Relace B vyžaduje sdílený zámek (S) na řádcích tabulky SalesLT.Product, včetně řádku, který uzamyká Relace A. Relace B je blokována na SalesLT.Product.

Vraťte se do relace A. Spusťte následující příkaz jazyka Transact-SQL. Tím se spustí druhý příkaz UPDATE jako součást otevřené transakce.

UPDATE SalesLT.ProductDescription
    SET Description = Description
FROM SalesLT.ProductDescription AS pd
     INNER JOIN SalesLT.ProductModelProductDescription AS pmpd
         ON pd.ProductDescriptionID = pmpd.ProductDescriptionID
     INNER JOIN SalesLT.ProductModel AS pm
         ON pmpd.ProductModelID = pm.ProductModelID
     INNER JOIN SalesLT.Product AS p
         ON pm.ProductModelID = p.ProductModelID
WHERE p.Color = 'Red';

Druhý příkaz aktualizace v Relace A je blokován Relace B na SalesLT.ProductDescription.

Relace A a Relace B se teď vzájemně blokují. Žádná transakce nemůže pokračovat, protože každý potřebuje prostředek, který je uzamčen jiným.

Po několika sekundách monitor zablokování zjistí, že transakce v relaci Session A a relaci Session B se vzájemně blokují a že ani jedna nemůže pokračovat. Měli byste vidět zablokování, kdy je relace A zvolena jako oběť zablokování. V relaci A se zobrazí chybová zpráva s textem podobným tomuto:

Msg 1205, Level 13, State 51, Line 7
Transaction (Process ID 91) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Sezení B úspěšně dokončeno.

Pokud jste v Azure portálu nastavili upozornění na deadlock, měli byste krátce po zablokování obdržet oznámení.

Zobrazení grafů vzájemného zablokování z relace XEvents

Pokud nastavíte relaci XEvents pro shromažďování zablokování a po spuštění relace dojde k zablokování, můžete zobrazit interaktivní grafické zobrazení grafu zablokování a XML pro tento graf.

Existují různé metody pro získání informací o vzájemném zablokování pro cílový prvek kruhové vyrovnávací paměti a cílové prvky souboru událostí. Vyberte cíl, který jste použili pro relaci XEvents:

Cíl kruhové vyrovnávací paměti

Pokud nastavíte relaci XEvents pro zápis do vyrovnávací paměti kruhu, můžete získat informace o vzájemném zablokování pomocí následujícího příkazu Transact-SQL. Před spuštěním dotazu nahraďte hodnotu @tracename názvem vaší relace XEvents.

DECLARE @tracename AS sysname = N'deadlocks';

WITH ring_buffer
AS (SELECT CAST (target_data AS XML) AS rb
    FROM sys.dm_xe_database_sessions AS s
         INNER JOIN sys.dm_xe_database_session_targets AS t
             ON CAST (t.event_session_address AS BINARY (8)) = CAST (s.address AS BINARY (8))
    WHERE s.name = @tracename
          AND t.target_name = N'ring_buffer'),
 dx
AS (SELECT dxdr.evtdata.query('.') AS deadlock_xml_deadlock_report
    FROM ring_buffer
CROSS APPLY rb.nodes('/RingBufferTarget/event[@name=''database_xml_deadlock_report'']') AS dxdr(evtdata))
SELECT d.query('/event/data[@name=''deadlock_cycle_id'']/value').value('(/value)[1]', 'int') AS [deadlock_cycle_id],
       d.value('(/event/@timestamp)[1]', 'DateTime2') AS [deadlock_timestamp],
       d.query('/event/data[@name=''database_name'']/value').value('(/value)[1]', 'nvarchar(256)') AS [database_name],
       d.query('/event/data[@name=''xml_report'']/value/deadlock') AS deadlock_xml,
       LTRIM(RTRIM(REPLACE(REPLACE(d.value('.', 'nvarchar(2000)'), CHAR(10), ' '), CHAR(13), ' '))) AS query_text
FROM dx
CROSS APPLY deadlock_xml_deadlock_report.nodes('(/event/data/value/deadlock/process-list/process/inputbuf)') AS ib(d)
ORDER BY [deadlock_timestamp] DESC;
GO

Cílový soubor události

Pokud nastavíte relaci XEvents pro zápis do souboru události, můžete si soubory stáhnout z portálu Azure a zobrazit je lokálně, nebo můžete dotazovat soubory událostí pomocí jazyka Transact-SQL.

Stahování souborů z webu Azure Portal se doporučuje, protože tato metoda k dotazování dat relace nevyžaduje použití databázových prostředků.

Volitelně restartujte relaci XEvents.

Pokud je aktuálně spuštěná relace rozšířených událostí a zapisuje data do cílového souboru událostí, kontejner objektů blob, do kterého se zapisuje, bude mít v Azure portálu stav Lease jako Zapůjčeno. Velikost je maximální velikost souboru. Pokud chcete stáhnout menší soubor, můžete před stažením souborů zastavit a restartovat relaci rozšířených událostí. Toto způsobí, že soubor změní svůj stav pronájmu na Dostupnýa velikost souboru bude odpovídat prostoru používanému událostmi v souboru.

Pokud chcete zastavit a restartovat relaci XEvents, připojte se k databázi a spusťte následující transact-SQL. Před spuštěním kódu nahraďte název relace XEvents správnou hodnotou.

ALTER EVENT SESSION [deadlocks_eventfile] ON DATABASE
STATE = STOP;
GO

ALTER EVENT SESSION [deadlocks_eventfile] ON DATABASE
STATE = START;
GO

Stažení trasovacích souborů z webu Azure Portal

Pokud chcete zobrazit události zablokování, které byly shromážděny napříč více soubory, stáhněte soubory relace událostí do místního počítače a zobrazte soubory v aplikaci SSMS.

Poznámka:

Můžete také použít Průzkumník služby Azure Storage k rychlému a pohodlnému stahování souborů sezení událostí z kontejneru objektů blob ve službě Azure Storage.

Stažení souborů z webu Azure Portal:

1. Na webu Azure Portal přejděte k účtu úložiště, který je hostitelem vašeho kontejneru.
2. V části Úložiště dat vyberte Kontejnery.
3. Vyberte kontejner obsahující trasovací soubory XEvent.
4. Pro každý soubor, který chcete stáhnout, vyberte ... a pak Stáhnout.

Zobrazení trasovacích souborů XEvents v SSMS

Pokud jste stáhli více souborů, můžete otevřít události ze všech souborů společně v prohlížeči XEvents v nástroji SSMS. Postup:

1. Otevřete aplikaci SSMS.
2. Vyberte Soubor, pak Otevřít a potom Sloučit soubory rozšířených událostí....
3. Vyberte Přidat.
4. Přejděte do adresáře, do kterého jste stáhli soubory. Pomocí klávesy Shift vyberte více souborů.
5. Vyberte Otevřít.
6. V dialogovém okně Sloučit soubory rozšířených událostí vyberte OK.

Pokud jste stáhli jeden soubor, klikněte pravým tlačítkem myši na soubor a vyberte Otevřít s a pak SSMS. Tím se otevře prohlížeč XEvents v nástroji SSMS.

Procházejte mezi událostmi shromážděnými výběrem příslušného časového razítka. Pokud chcete zobrazit XML pro zablokování, dvojklikněte na řádek xml_report v dolním podokně.

Dotazování trasovacích souborů pomocí jazyka Transact-SQL

Důležité

Dotazování velkých (1 GB a větších) trasovacích souborů XEvents pomocí této metody se nedoporučuje, protože spotřebovává velké množství paměti v databázi nebo elastickém fondu.

Pokud chcete dotazovat trasovací soubory XEvents z kontejneru Azure Storage pomocí jazyka Transact-SQL, musíte zadat přesný název souboru trasování. Pokud chcete získat přístup k úložišti, musíte také spustit dotaz v kontextu databáze s přihlašovacími údaji, jinými slovy, stejnou databázi, která vytvořila soubory XEvents.

Spuštěním následujícího příkazu Transact-SQL zadejte dotaz na aktuálně aktivní trasovací soubor XEvents. Před spuštěním dotazu nahraďte @tracename názvem relace XEvents.

DECLARE @tracename AS sysname = N'deadlocks_eventfile', @filename AS NVARCHAR (2000);

WITH eft
AS (SELECT CAST (target_data AS XML) AS rb
    FROM sys.dm_xe_database_sessions AS s
         INNER JOIN sys.dm_xe_database_session_targets AS t
             ON CAST (t.event_session_address AS BINARY (8)) = CAST (s.address AS BINARY (8))
    WHERE s.name = @tracename
          AND t.target_name = N'event_file')
SELECT @filename = ft.evtdata.value('(@name)[1]', 'nvarchar(2000)')
FROM eft
CROSS APPLY rb.nodes('EventFileTarget/File') AS ft(evtdata);

WITH xevents
AS (SELECT CAST (event_data AS XML) AS ed
    FROM sys.fn_xe_file_target_read_file(@filename, NULL, NULL, NULL)),
 dx
AS (SELECT dxdr.evtdata.query('.') AS deadlock_xml_deadlock_report
    FROM xevents
CROSS APPLY ed.nodes('/event[@name=''database_xml_deadlock_report'']') AS dxdr(evtdata))
SELECT d.query('/event/data[@name=''deadlock_cycle_id'']/value').value('(/value)[1]', 'int') AS [deadlock_cycle_id],
       d.value('(/event/@timestamp)[1]', 'DateTime2') AS [deadlock_timestamp],
       d.query('/event/data[@name=''database_name'']/value').value('(/value)[1]', 'nvarchar(256)') AS [database_name],
       d.query('/event/data[@name=''xml_report'']/value/deadlock') AS deadlock_xml,
       LTRIM(RTRIM(REPLACE(REPLACE(d.value('.', 'nvarchar(2000)'), CHAR(10), ' '), CHAR(13), ' '))) AS query_text
FROM dx
CROSS APPLY deadlock_xml_deadlock_report.nodes('(/event/data/value/deadlock/process-list/process/inputbuf)') AS ib(d)
ORDER BY [deadlock_timestamp] DESC;
GO

Pokud chcete dotazovat soubory, které nejsou aktivní, přejděte na webu Azure Portal do účtu úložiště a kontejneru a identifikujte názvy souborů.

Spusťte následující dotaz v jazyce Transact-SQL na vaší databázi pro dotazování konkrétního souboru XEvents. Před spuštěním dotazu nahraďte název účtu úložiště, název kontejneru a název souboru v adrese URL pro @filename:

DECLARE @filename AS NVARCHAR (2000) = N'https://<storage-account>.blob.core.windows.net/yourcontainername/yourfilename.xel';

WITH xevents
AS (SELECT CAST (event_data AS XML) AS ed
    FROM sys.fn_xe_file_target_read_file(@filename, NULL, NULL, NULL)),
 dx
AS (SELECT dxdr.evtdata.query('.') AS deadlock_xml_deadlock_report
    FROM xevents
CROSS APPLY ed.nodes('/event[@name=''database_xml_deadlock_report'']') AS dxdr(evtdata))
SELECT d.query('/event/data[@name=''deadlock_cycle_id'']/value').value('(/value)[1]', 'int') AS [deadlock_cycle_id],
       d.value('(/event/@timestamp)[1]', 'DateTime2') AS [deadlock_timestamp],
       d.query('/event/data[@name=''database_name'']/value').value('(/value)[1]', 'nvarchar(256)') AS [database_name],
       d.query('/event/data[@name=''xml_report'']/value/deadlock') AS deadlock_xml,
       LTRIM(RTRIM(REPLACE(REPLACE(d.value('.', 'nvarchar(2000)'), CHAR(10), ' '), CHAR(13), ' '))) AS query_text
FROM dx
CROSS APPLY deadlock_xml_deadlock_report.nodes('(/event/data/value/deadlock/process-list/process/inputbuf)') AS ib(d)
ORDER BY [deadlock_timestamp] DESC;
GO

Zobrazení a uložení grafu vzájemného zablokování v JAZYCE XML

Zobrazení grafu vzájemného zablokování ve formátu XML umožňuje zkopírovat inputbuffer příkazy Jazyka Transact-SQL zapojené do zablokování. Zablokování můžete analyzovat také v textovém formátu.

Pokud jste použili dotaz Transact-SQL ke zjištění informací o grafu zablokování, abyste mohli zobrazit XML grafu zablokování, vyberte hodnotu ve sloupci deadlock_xml z libovolného řádku, který obsahuje tuto hodnotu, a otevřete XML grafu zablokování v novém okně v SSMS.

XML pro tento příklad grafu deadlocku je:

<deadlock>
  <victim-list>
    <victimProcess id="process24756e75088" />
  </victim-list>
  <process-list>
    <process id="process24756e75088" taskpriority="0" logused="6528" waitresource="KEY: 8:72057594045202432 (98ec012aa510)" waittime="192" ownerId="1011123" transactionname="user_transaction" lasttranstarted="2022-03-08T15:44:43.490" XDES="0x2475c980428" lockMode="U" schedulerid="3" kpid="30192" status="suspended" spid="89" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2022-03-08T15:44:49.250" lastbatchcompleted="2022-03-08T15:44:49.210" lastattention="1900-01-01T00:00:00.210" clientapp="Microsoft SQL Server Management Studio - Query" hostname="LAPTOP-CHRISQ" hostpid="16716" loginname="chrisqpublic" isolationlevel="read committed (2)" xactid="1011123" currentdb="8" currentdbname="AdventureWorksLT" lockTimeout="4294967295" clientoption1="671096864" clientoption2="128056">
      <executionStack>
        <frame procname="unknown" queryhash="0xef52b103e8b9b8ca" queryplanhash="0x02b0f58d7730f798" line="1" stmtstart="2" stmtend="792" sqlhandle="0x02000000c58b8f1e24e8f104a930776e21254b1771f92a520000000000000000000000000000000000000000">
unknown    </frame>
      </executionStack>
      <inputbuf>
    UPDATE SalesLT.ProductDescription SET Description = Description
        FROM SalesLT.ProductDescription as pd
        JOIN SalesLT.ProductModelProductDescription as pmpd on
            pd.ProductDescriptionID = pmpd.ProductDescriptionID
        JOIN SalesLT.ProductModel as pm on
            pmpd.ProductModelID = pm.ProductModelID
        JOIN SalesLT.Product as p on
            pm.ProductModelID=p.ProductModelID
        WHERE p.Color = 'Red'   </inputbuf>
    </process>
    <process id="process2476d07d088" taskpriority="0" logused="11360" waitresource="KEY: 8:72057594045267968 (39e18040972e)" waittime="2641" ownerId="1013536" transactionname="UPDATE" lasttranstarted="2022-03-08T15:44:46.807" XDES="0x2475ca80428" lockMode="S" schedulerid="2" kpid="94040" status="suspended" spid="95" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2022-03-08T15:44:46.807" lastbatchcompleted="2022-03-08T15:44:46.760" lastattention="1900-01-01T00:00:00.760" clientapp="Microsoft SQL Server Management Studio - Query" hostname="LAPTOP-CHRISQ" hostpid="16716" loginname="chrisqpublic" isolationlevel="read committed (2)" xactid="1013536" currentdb="8" currentdbname="AdventureWorksLT" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
      <executionStack>
        <frame procname="unknown" queryhash="0xef52b103e8b9b8ca" queryplanhash="0x02b0f58d7730f798" line="1" stmtstart="2" stmtend="798" sqlhandle="0x020000002c85bb06327c0852c0be840fc1e30efce2b7c8090000000000000000000000000000000000000000">
unknown    </frame>
      </executionStack>
      <inputbuf>
    UPDATE SalesLT.ProductDescription SET Description = Description
        FROM SalesLT.ProductDescription as pd
        JOIN SalesLT.ProductModelProductDescription as pmpd on
            pd.ProductDescriptionID = pmpd.ProductDescriptionID
        JOIN SalesLT.ProductModel as pm on
            pmpd.ProductModelID = pm.ProductModelID
        JOIN SalesLT.Product as p on
            pm.ProductModelID=p.ProductModelID
        WHERE p.Color = 'Silver';   </inputbuf>
    </process>
  </process-list>
  <resource-list>
    <keylock hobtid="72057594045202432" dbid="8" objectname="9e011567-2446-4213-9617-bad2624ccc30.SalesLT.ProductDescription" indexname="PK_ProductDescription_ProductDescriptionID" id="lock2474df12080" mode="U" associatedObjectId="72057594045202432">
      <owner-list>
        <owner id="process2476d07d088" mode="U" />
      </owner-list>
      <waiter-list>
        <waiter id="process24756e75088" mode="U" requestType="wait" />
      </waiter-list>
    </keylock>
    <keylock hobtid="72057594045267968" dbid="8" objectname="9e011567-2446-4213-9617-bad2624ccc30.SalesLT.Product" indexname="PK_Product_ProductID" id="lock2474b588580" mode="X" associatedObjectId="72057594045267968">
      <owner-list>
        <owner id="process24756e75088" mode="X" />
      </owner-list>
      <waiter-list>
        <waiter id="process2476d07d088" mode="S" requestType="wait" />
      </waiter-list>
    </keylock>
  </resource-list>
</deadlock>

Uložení grafu vzájemného zablokování jako souboru XML:

1. Vyberte Soubor a Uložit jako....
2. Ponechte hodnotu Uložit jako typ jako výchozí soubory XML (*.xml)
3. Nastavte název souboru na název podle vašeho výběru.
4. Zvolte Uložit.

Uložení grafu zablokování jako souboru XDL, který se dá v SSMS zobrazit interaktivně

Zobrazení interaktivní reprezentace grafu vzájemného zablokování může být užitečné k získání rychlého přehledu procesů a prostředků zapojených do zablokování a rychlé identifikaci oběti zablokování.

Uložení grafu zablokování jako souboru, který může SSMS graficky zobrazit:

1. Výběrem hodnoty ve sloupci v libovolném deadlock_xml řádku otevřete graf zablokování ve formátu XML v novém okně v SSMS.

2. Vyberte Soubor a Uložit jako....

3. Nastavte Uložit jako typ na Všechny soubory.

4. Nastavte Název souboru na název podle vašeho výběru s příponou nastavenou na .xdl.

5. Zvolte Uložit.

   

6. Zavřete soubor kliknutím na X na kartě v horní části okna, nebo vyberte Soubor a poté Zavřít.

7. Otevřete soubor v aplikaci SSMS výběrem Soubor, pak Otevřít, pak Soubor. Vyberte soubor, který jste uložili s příponou .xdl .

   Graf vzájemného zablokování se nyní zobrazuje v SSMS s vizuální reprezentací procesů a zdrojů, které jsou do něj zapojeny.

   

Analýza zablokování pro Azure SQL Database

Graf vzájemného zablokování má obvykle tři uzly.

• Seznam obětí. Identifikátor procesu oběti deadlocku.

• Seznam procesů Informace o všech procesech, které se na zablokování podílejí. Grafy zablokování používají termín "proces" k označení sezení, které spouští transakci.

• Seznam zdrojů Informace o prostředcích, které jsou součástí zablokování.

Při analýze problému se zablokováním je užitečné procházet tyto uzly.

Seznam obětí zablokování

Seznam obětí vzájemného zablokování zobrazuje proces, který byl vybrán jako oběť vzájemného zablokování. Ve vizuální reprezentaci grafu zablokování jsou procesy reprezentovány ovály. Proces oběti zablokování má "X" nakreslený přes ovál.

V XML zobrazení grafu zablokovánívictim-list uzel představuje ID procesu, který byl obětí zablokování.

V našem příkladu zablokování je obětí proces s ID process24756e75088. Tento identifikátor můžeme použít při zkoumání uzlů seznamu procesů a seznamu prostředků, abychom se dozvěděli více o procesu oběti a prostředcích, které uzamykal nebo které žádal o uzamčení.

Seznam procesů zablokování

Seznam procesů zablokování je bohatým zdrojem informací o transakcích zapojených do zablokování.

Grafické znázornění grafu vzájemného zablokování zobrazuje pouze podmnožinu informací obsažených v souboru XML grafu vzájemného zablokování. Ovály v grafu zablokování představují proces a zobrazují informace, včetně:

• ID relace, označované také jako SPID.

• Priorita vzájemného zablokování relace. Pokud dvě relace mají různé priority zablokování, je relace s nižší prioritou zvolena jako oběť. V tomto příkladu mají obě sezení stejnou prioritu vzájemného zablokování.

• Množství transakčního protokolu používaného relací v bajtech. Pokud mají obě relace stejnou prioritu vzájemného zablokování, monitor vzájemného zablokování zvolí relaci, která je levnější, aby se vrátila zpět jako oběť vzájemného zablokování. Náklady jsou určeny porovnáním počtu bajtů protokolu zapsaných k bodě každé transakce.

  V našem příkladu zablokování session_id 89 použilo nižší množství transakčního protokolu a bylo vybráno jako oběť zablokování.

Kromě toho můžete zobrazit vstupní vyrovnávací paměť posledního příkazu spuštěného v každé relaci před zablokováním tak, že na každý proces najedete myší. Vstupní vyrovnávací paměť se zobrazí v popisku.

Další informace o procesech jsou k dispozici v XML pohledu na graf zablokování, včetně:

• Identifikace informací pro relaci, například název klienta, název hostitele a přihlašovací jméno.

• Hash plánu dotazu pro poslední příkaz spuštěný každou relací před deadlockem. Hodnota hash plánu dotazu je užitečná pro načtení dalších informací o dotazu z úložiště dotazů.

V našem příkladu zablokování:

• Vidíme, že obě relace byly spuštěny pomocí klienta SSMS pod přihlašovacím jménem chrisqpublic.

• Hodnota hash plánu dotazu, posledního příkazu spuštěného před vzájemným zablokováním naší oběti, je 0x02b0f58d7730f798. Text tohoto příkazu vidíme ve vstupní vyrovnávací paměti.

• Hodnota hash plánu dotazu, posledního příkazu spuštěného druhou relací v našem zablokování, je také 0x02b0f58d7730f798. Text tohoto příkazu vidíme ve vstupní vyrovnávací paměti. V tomto případě mají oba dotazy stejnou hodnotu hash plánu dotazů, protože dotazy jsou stejné, s výjimkou literálové hodnoty použité jako predikát rovnosti.

Tyto hodnoty používáme dále v tomto článku, abychom našli další informace v Úložišti dotazů.

Omezení vstupní vyrovnávací paměti v seznamu procesů zablokování

Existují určitá omezení týkající se informací o vstupní vyrovnávací paměti v seznamu procesů blokování.

Text dotazu může být zkrácen ve vstupní vyrovnávací paměti. Vstupní vyrovnávací paměť je omezená na prvních 4 000 znaků příkazu, který se spouští.

Některé příkazy zapojené do vzájemného zablokování navíc nemusí být zahrnuty do grafu vzájemného zablokování. V našem příkladu relace A spustila dva příkazy aktualizace v rámci jedné transakce. Do grafu vzájemného zablokování je zahrnut pouze druhý aktualizační příkaz, tedy ten, který způsobil zablokování. První aktualizační příkaz spuštěný Session A hrál roli v deadlocku tím, že zablokoval Session B. Vstupní vyrovnávací paměť query_hash a související informace pro první příkaz spuštěný Session A nejsou zahrnuty v grafu deadlocku.

Pokud chcete identifikovat plné příkazy Transact-SQL spuštěné v transakci s více příkazy, které se účastní zablokování, musíte buď najít relevantní informace v uložené proceduře nebo v kódu aplikace, který spustil dotaz, nebo spustit trasování pomocí Rozšířených událostí, abyste zachytili kompletní příkazy spuštěné sezeními během zablokování. Pokud je příkaz zapojený do zablokování zkrácený a ve vstupní vyrovnávací paměti se zobrazí pouze částečné Transact-SQL, můžete najít Transact-SQL příkazu v úložišti dotazů pomocí plánu provádění.

Seznam prostředků zablokování

Seznam prostředků vzájemného zablokování ukazuje, které prostředky zámku jsou vlastněny a čekají na ně procesy ve deadlocku.

Prostředky jsou reprezentovány obdélníky ve vizuální reprezentaci zablokování:

Poznámka:

Názvy databází jsou v grafech vzájemného zablokování pro Azure SQL Database zobrazovány jako identifikátory GUID (uniqueidentifier). Toto je physical_database_name pro databázi uvedenou v dynamických pohledech správy sys.databases a sys.dm_user_db_resource_governance.

V tomto příkladu zablokování:

• Oběť vzájemného zablokování, kterou jsme označili jako Relace A:

  • Vlastní výhradní zámek (X) na klíči v indexu PK_Product_ProductID na tabulce SalesLT.Product.

  • Vyžádá si zámek aktualizace (U) na klíč indexu PK_ProductDescription_ProductDescriptionID v SalesLT.ProductDescription tabulce.

• Druhý proces, který jsme označili jako Sezení B:

  • Vlastní zámek aktualizace (U) na klíči v indexu PK_ProductDescription_ProductDescriptionID na tabulce SalesLT.ProductDescription.

  • Vyžádá si sdílený zámek (S) na klíč v indexu PK_ProductDescription_ProductDescriptionID v SalesLT.ProductDescription tabulce.

Stejné informace vidíme v xml grafu vzájemného zablokování v uzlu seznamu prostředků.

Vyhledání plánů spouštění dotazů v úložišti dotazů

Často je užitečné prozkoumat plány provádění dotazů na příkazy zapojené do zablokování. Tyto plány provádění lze často nalézt v úložišti dotazů pomocí hodnoty hash plánu dotazu ze zobrazení XML seznamu procesů grafu zablokování.

Tento dotaz Transact-SQL vyhledává plány dotazů, které odpovídají hash kódu plánu dotazu, který jsme našli v našem příkladu zablokování. Připojte se k uživatelské databázi ve službě Azure SQL Database a spusťte dotaz.

DECLARE @query_plan_hash AS BINARY (8) = 0x02b0f58d7730f798;

SELECT qrsi.end_time AS interval_end_time,
       qs.query_id,
       qp.plan_id,
       qt.query_sql_text,
       TRY_CAST (qp.query_plan AS XML) AS query_plan,
       qrs.count_executions
FROM sys.query_store_query AS qs
     INNER JOIN sys.query_store_query_text AS qt
         ON qs.query_text_id = qt.query_text_id
     INNER JOIN sys.query_store_plan AS qp
         ON qs.query_id = qp.query_id
     INNER JOIN sys.query_store_runtime_stats AS qrs
         ON qp.plan_id = qrs.plan_id
     INNER JOIN sys.query_store_runtime_stats_interval AS qrsi
         ON qrs.runtime_stats_interval_id = qrsi.runtime_stats_interval_id
WHERE query_plan_hash = @query_plan_hash
ORDER BY interval_end_time, query_id;
GO

V závislosti na nastavení CLEANUP_POLICY úložiště dotazů nebo QUERY_CAPTURE_MODEmožná nebudete moct z úložiště dotazů získat plán provedení dotazu. V takovém případě můžete často získat potřebné informace zobrazením odhadovaného plánu spuštění dotazu.

Hledejte vzory, které zvyšují blokování.

Při zkoumání plánů provádění dotazů zapojených do uvízlých procesů se zaměřte na vzory, které mohou přispívat k blokování a uvíznutí.

• Prohledávání tabulek nebo indexů Při spouštění dotazů, které upravují data v rámci RCSI, se výběr řádků, které se mají aktualizovat, provádí pomocí blokujícího skenování, kdy se na řádku dat přebírá zámek aktualizace (U) během čtení datových hodnot. Pokud řádek dat nesplňuje kritéria aktualizace, uvolní se zámek aktualizace a další řádek se uzamkne a zkontroluje.

  Ladění indexů, které pomáhají modifikačním dotazům efektivněji najít řádky, snižuje počet vydaných aktualizačních zámků. Tím se sníží pravděpodobnost blokování a mrtvých bodů.

• Indexovaná zobrazení odkazující na více než jednu tabulku Když upravíte tabulku odkazovanou v indexovaném zobrazení, musí databázový stroj udržovat také indexované zobrazení. To vyžaduje odebrání více zámků a může vést ke zvýšení blokování a patových situací. Indexovaná zobrazení mohou také způsobit, že se operace aktualizace provádí interně v režimu izolace čtení s potvrzením.

• Úpravy sloupců, na které se odkazuje v omezeních cizích klíčů Když upravíte sloupce v tabulce, na které odkazuje omezení FOREIGN KEY, musí databázový stroj hledat související řádky v odkazující tabulce. Řádkové verze nelze pro tyto operace čtení použít. V případech, kdy jsou povoleny kaskádové aktualizace nebo odstranění, může být úroveň izolace eskalována k serializovatelné po dobu trvání příkazu pro ochranu proti fantomovým vložením.

• Náznaky zamykání. Vyhledejte nápovědy tabulky, které určují úrovně izolace vyžadující více zámků. Tyto rady zahrnují HOLDLOCK (což je ekvivalent serializable), SERIALIZABLE, READCOMMITTEDLOCK (který zakazuje RCSI) a REPEATABLEREAD. Kromě toho mohou nápovědy, jako PAGLOCK, TABLOCK, UPDLOCK a XLOCK, zvýšit rizika blokování a deadlocků.

  Pokud jsou tyto nápovědy zavedené, zjistěte, proč byly implementovány. Tyto tipy mohou zabránit souběžným podmínkám a zajistit správnost dat. Tyto rady je možné ponechat na místě a v případě potřeby zabránit budoucímu zablokování pomocí alternativní metody v části tohoto článku Zabránění opětovnému výskytu zablokování.

  Poznámka:

  Přečtěte si další informace o chování při úpravě dat pomocí verzování řádků v průvodci uzamčení transakcí a verzování řádků.

Při zkoumání úplného kódu transakce, ať už v plánu provádění nebo v kódu dotazu aplikace, vyhledejte další problematické vzory:

• Interakce uživatele v transakcích. Interakce uživatele uvnitř explicitní transakce obsahující více příkazů významně zvyšuje dobu trvání transakce. To zvyšuje pravděpodobnost, že se tyto transakce překrývají a že dochází k blokování a vzniku zablokování.

  Podobně držení otevřené transakce a dotazování nesouvisející databáze nebo systému během transakce výrazně zvyšuje pravděpodobnost blokování a uvíznutí.

• Transakce přistupující k objektům v různém pořadí. Dochází k zablokování méně často, když souběžné explicitní transakce skládající se z více příkazů se řídí stejnými vzory a přistupují k objektům ve stejném pořadí.

Zabránění opakovanému zablokování

K dispozici je několik technik, které brání opakovanému zablokování, jako je optimalizace indexu, vynucení plánů pomocí Query Store a úprava dotazů Transact-SQL.

• Zkontrolujte clusterovaný index tabulky. Většina tabulek těží z clusterovaných indexů, ale často se tabulky implementují jako haldy omylem.

  Jedním ze způsobů, jak zkontrolovat clusterovaný index, je použití sp_helpindex systémové uložené procedury. Můžeme například zobrazit souhrn indexů v SalesLT.Product tabulce spuštěním následujícího příkazu:

  EXECUTE sp_helpindex 'SalesLT.Product';
  GO
  

  Zkontrolujte sloupec index_description. Tabulka může mít pouze jeden clusterovaný index. Pokud byl pro tabulku implementován clusterovaný index, index_description obsahuje slovo clustered.

  Pokud není k dispozici žádný clusterovaný index, tabulka je haldou. V tomto případě zkontrolujte, jestli byla tabulka záměrně vytvořená jako hromada k vyřešení konkrétního problému s výkonem. Zvažte implementaci clusterovaného indexu na základě pokynů pro návrh clusterovaného indexu.

  V některých případech může vytváření nebo ladění clusterovaného indexu omezit nebo eliminovat blokování v zámkových situacích. V jiných případech můžete použít další techniku, jako jsou ostatní v tomto seznamu.

• Vytvoření nebo úprava neclusterovaných indexů. Ladění neclusterovaných indexů může vašim dotazům na úpravy pomoct rychleji najít data, která se mají aktualizovat, což snižuje počet požadovaných zámků aktualizací.

  V našem příkladu zablokování obsahuje plán provádění dotazu nalezený v Query Store skenování clusterovaného indexu PK_Product_ProductID. Graf vzájemného zablokování naznačuje, že čekání na sdílený (S) zámek na tomto indexu je součástí vzájemného zablokování.

  

  Tato kontrola indexu se provádí, protože náš aktualizační dotaz potřebuje upravit indexované zobrazení s názvem vProductAndDescription. Jak je uvedeno v části s názvem Hledání vzorů, které vedou ke zvýšení blokování tohoto článku, indexovaná zobrazení odkazující na více tabulek mohou zvýšit blokování a pravděpodobnost vzájemného blokování.

  Pokud v databázi vytvoříme následující neclusterovaný index AdventureWorksLT , který "pokrývá" sloupce z SalesLT.Product odkazovaného indexovaného zobrazení, pomůže dotaz najít řádky mnohem efektivněji:

  CREATE INDEX IX_Product_ProductID_Name_ProductModelID
      ON SalesLT.Product(ProductID, Name, ProductModelID);
  GO
  

  Po vytvoření tohoto indexu se zablokování už nebude opakovat.

  Pokud zablokování zahrnuje úpravy sloupců, na něž se odkazuje v omezeních cizího klíče, zajistěte, aby indexy na odkazující tabulce FOREIGN KEY efektivně podporovaly nalezení souvisejících řádků.

  Indexy sice můžou v některých případech výrazně zlepšit výkon dotazů, ale indexy mají také režijní náklady a náklady na správu. Projděte si obecné pokyny k návrhu indexů, které vám pomůžou posoudit výhody indexů před vytvořením indexů, zejména širokých indexů a indexů u velkých tabulek.

• Vyhodnoťte hodnotu indexovaných zobrazení. Další možností, jak zabránit opakovanému vytvoření deadlocku v našem příkladu, je zrušit indexované zobrazení SalesLT.vProductAndDescription. Pokud se toto indexované zobrazení nepoužívá, snižuje se tím režijní náklady na údržbu indexovaného zobrazení v průběhu času.

• Použijte izolaci snímků. V některých případech může nastavení úrovně izolace transakce na úroveň snímku u jedné nebo více transakcí zahrnutých v mrtvém uzlu zabránit opakovanému blokování a vzniku dalších mrtvých uzlů.

  Tato technika bude pravděpodobně úspěšná při použití u příkazů SELECT, pokud je v databázi zakázán režim potvrzeného snímku čtení. Pokud je zakázaný snímek potvrzený pro čtení, SELECT dotazy používající úroveň izolace potvrzené čtení vyžadují sdílené zámky (S). Izolace pomocí snímků u těchto transakcí eliminuje potřebu sdílených zámků, což může zabránit blokování a vzniku zablokování.

  V databázích, kde je povolena izolace snímku pro potvrzené čtení, dotazy SELECT nevyžadují sdílené (S) zámky, takže mezi transakcemi, které upravují data, pravděpodobněji vznikají patové situace. V případech, kdy dojde k patové situaci mezi více transakcemi, které upravují data, může izolace pomocí snímků vést ke konfliktu při aktualizaci místo zablokování. To podobně vyžaduje, aby jedna z transakcí zkusila operaci zopakovat.

• Vynuťte plán s úložištěm dotazů. Můžete zjistit, že jeden z dotazů v deadlocku má více exekučních plánů a zablokování nastane jen tehdy, když se použije specifický plán. Můžete zabránit opakovanému zablokování vynucením plánu v úložišti dotazů.

• Upravte transact-SQL. Možná budete muset upravit Transact-SQL, abyste zabránili opětovnému zablokování. Úpravy jazyka Transact-SQL by se měly provádět pečlivě a změny by měly být pečlivě testovány, aby se zajistila správnost dat při souběžném spuštění úprav. Při přepisu jazyka Transact-SQL zvažte následující:

  • Řazení příkazů v transakcích tak, aby přistupovaly k objektům ve stejném pořadí.

  • Pokud je to možné, rozdělte transakce na menší transakce.

  • Pokud je to potřeba, použijte nápovědu k dotazu k optimalizaci výkonu. Nápovědu můžete použít bez nutnosti měnit kód aplikace pomocí Úložiště dotazů.

Najděte více způsobů, jak minimalizovat zablokování v průvodci zablokováním.

Poznámka:

V některých případech můžete upravit prioritu zablokování jedné nebo více relací, které jsou součástí zablokování, pokud je důležité, aby se jedna z relací úspěšně dokončila bez opakování, nebo pokud některý z dotazů zapojených do zablokování není kritický a měl by být vždy zvolen jako oběť. I když to nezabrání opětovnému zablokování, může to snížit účinek budoucích zablokování.

Zrušení relace XEvents

Relaci XEvents můžete nechat sbírat údaje o zablokování na kritických databázích po dlouhou dobu. Pokud použijete cíl souboru událostí, může to vést k vytvoření velkých souborů, pokud dojde k několika zablokovaným situacím. V rámci aktivního trasování můžete odstranit soubory objektů blob z Azure Storage, s výjimkou souboru, do kterého se v současnosti zapisuje.

Pokud chcete odebrat relaci XEvents, použití příkazu Transact-SQL pro zrušení relace je stejné, bez ohledu na vybraný cílový typ.

Pokud chcete odebrat relaci XEvents, spusťte následující transact-SQL. Před spuštěním kódu nahraďte název seance příslušnou hodnotou.

ALTER EVENT SESSION [deadlocks] ON DATABASE
STATE = STOP;
GO

DROP EVENT SESSION [deadlocks] ON DATABASE;
GO

Použití Průzkumníka služby Azure Storage

Průzkumník služby Azure Storage je samostatná aplikace, která zjednodušuje práci s cíli souborů událostí uloženými v objektech blob ve službě Azure Storage. Služba Průzkumník úložiště vám umožňuje:

• Vytvořte kontejner objektů blob, který bude sloužit k uchování dat relace XEvent.

• Získejte sdílený přístupový podpis (SAS) pro kontejner objektů blob.

  • Jak je uvedeno v Shromažďování grafů vzájemného zablokování ve službě Azure SQL Database pomocí rozšířených událostí, jsou vyžadována oprávnění ke čtení, zápisu a seznamování.

  • Odeberte jakýkoli počáteční znak ? z Query string, abyste tuto hodnotu použili jako tajný kód při vytváření přihlašovacích údajů s omezeným oborem databáze.

• Umožňuje zobrazit a stáhnout rozšířené soubory událostí z kontejneru objektů blob.

Stáhnout Průzkumníka služby Azure Storage.

Související obsah

• Vysvětlení a řešení blokujících problémů
• Průvodce uzamykáním transakcí a verzováním řádků
• Průvodce vzájemným zablokováním
• NASTAVENÍ ÚROVNĚ IZOLACE TRANSAKCÍ
• Azure SQL Database: Vylepšení ladění výkonu pomocí automatického ladění
• Zajištění konzistentního výkonu s využitím Azure SQL
• logika opakování pro přechodné chyby