🥇Dekódujeme Key a Page WaitResource v deadlockech a zablokováních

Pokud používáte zprávu o blokacích (blocked process report) nebo shromažďujete grafy deadlocků poskytované SQL Serverem, čas od času se setkáte s takovými informacemi:

waitresource=“PAGE: 6:3:70133“
waitresource=“KEY: 6:72057594041991168 (ce52f92a058c)“

Někdy v tom obrovském XML, které zkoumáte, najdete více informací (grafy deadlocků obsahují seznam zdrojů, který pomáhá zjistit názvy objektu a indexu), ale ne vždy.

Tento text vám pomůže je rozluštit.

Veškeré informace, které zde jsou, jsou na internetu na různých místech, jen jsou silně rozptýlené! Chci je všechny shromáždit — od DBCC PAGE po hobt_id a nedokumentované %%physloc%% a %%lockres%% funkce.

Nejprve si promluvíme o čekání na PAGE blokace a poté přejdeme k KEY blokacím.

1) waitresource=“PAGE: 6:3:70133” = Database_Id: FileId: PageNumber

Pokud váš dotaz čeká na PAGE blokaci, SQL Server vám poskytne adresu této stránky.

Rozložením „PAGE: 6:3:70133“ dostáváme:

database_id = 6
data_file_id = 3
page_number = 70133

1.1) Rozluštění database_id

Najdeme název databáze pomocí dotazu:

SELECT 
    name 
FROM sys.databases 
WHERE database_id=6;
GO

Toto je veřejná databáze WideWorldImporters na mém SQL Serveru.

1.2) Hledáme název datového souboru — pokud vás to zajímá

Budeme používat data_file_id v dalším kroku k nalezení názvu tabulky. Můžete jednoduše přejít k dalšímu kroku, ale pokud vás zajímá název souboru, můžete jej najít provedením dotazu v kontextu nalezené databáze, vložením data_file_id do tohoto dotazu:

USE WideWorldImporters;
GO
SELECT 
    name, 
    physical_name
FROM sys.database_files
WHERE file_id = 3;
GO

V databázi WideWorldImporters je to soubor s názvem WWI_UserData a je obnoven u mě v C:MSSQLDATAWideWorldImporters_UserData.ndf. (Ups, nachytali jste mě, jak ukládám soubory na disk se systémem! Ne! To bylo trapné).

1.3) Získání názvu objektu z DBCC PAGE

Nyní víme, že stránka #70133 v datovém souboru 3 patří databázi WorldWideImporters. Můžeme se podívat na obsah této stránky pomocí nedokumentovaného DBCC PAGE a trace flagu 3604.
Poznámka: Dávám přednost používání DBCC PAGE na obnovené kopii z zálohy někde na jiném serveru, protože je to nedokumentovaná záležitost. V některých případech to může vést ke vzniku dumpu (pozn. překladatele — odkaz, bohužel, vede nikam, ale soudě podle url se jedná o filtered indexy).

/* This trace flag makes DBCC PAGE output go to our Messages tab
instead of the SQL Server Error Log file */
DBCC TRACEON (3604);
GO
/* DBCC PAGE (DatabaseName, FileNumber, PageNumber, DumpStyle)*/
DBCC PAGE ('WideWorldImporters',3,70133,2);
GO

Procházením výsledků lze nalézt object_id a index_id.

Skoro hotovo! Nyní můžete najít názvy tabulky a indexu pomocí dotazu:

USE WideWorldImporters;
GO
SELECT 
    sc.name as schema_name, 
    so.name as object_name, 
    si.name as index_name
FROM sys.objects as so 
JOIN sys.indexes as si on 
    so.object_id=si.object_id
JOIN sys.schemas AS sc on 
    so.schema_id=sc.schema_id
WHERE 
    so.object_id = 94623380
    and si.index_id = 1;
GO

A tady vidíme, že čekání na zablokování bylo na indexu PK_Sales_OrderLines tabulky Sales.OrderLines.

Poznámka: V SQL Server 2014 a novějších lze také najít název objektu pomocí nedokumentovaného DMO sys.dm_db_database_page_allocations. Ale budete muset dotazovat každou stránku v databázi, což vypadá docela špatně pro velké databáze, takže jsem použila DBCC PAGE.

1.4) Můžeme vidět data na té stránce, která byla zablokována?

No, možná. Ale… jste si jisti, že to opravdu potřebujete?
Je to pomalé, i na malých tabulkách. Ale je to tak trochu zajímavé, takže když už jste došli až sem… pojďme se bavit o %%physloc%%!

%%physloc%% je nedokumentovaný kousek magie, který vrací fyzický identifikátor pro každý záznam. Můžete použít %%physloc%% spolu se sys.fn_PhysLocFormatter v SQL Server 2008 a novějších..

Teď, když víme, že jsme chtěli uvalit zablokování na stránku v Sales.OrderLines, můžeme se podívat na všechna data v této tabulce, která jsou uložena v datovém souboru #3 na stránce #70133, pomocí tohoto dotazu:

Use WideWorldImporters;
GO
SELECT 
    sys.fn_PhysLocFormatter (%%physloc%%),
    *
FROM Sales.OrderLines (NOLOCK)
WHERE sys.fn_PhysLocFormatter (%%physloc%%) like '(3:70133%'
GO

Jak jsem říkala — je to pomalé, i na malých tabulkách. Přidala jsem k dotazu NOLOCK, protože stejně nemáme žádné záruky, že data, na která se chceme podívat, jsou ta stejná, která byla v době, kdy byla zablokování zjištěna — takže můžeme v klidu provádět nezaručené čtení.
Ale hurá, dotaz mi vrací těch 25 řádků, o které naše dotaz bojovalo.

Dost o PAGE blokacích. Co když čekáme na KEY blokaci?

2) waitresource=“KEY: 6:72057594041991168 (ce52f92a058c)” = Database_Id, HOBT_Id (magický hash, který lze dešifrovat pomocí %%lockres%%, pokud opravdu chcete)

Pokud se váš dotaz pokouší uvalit zablokování na záznam v indexu a zůstane sám zablokován, dostanete zcela jiný typ adresy.
Rozdělením “6:72057594041991168 (ce52f92a058c)” na části dostáváme:

database_id = 6
hobt_id = 72057594041991168
magický hash = (ce52f92a058c)

2.1) Dešifrujeme database_id

Funguje to přesně tak, jak to bylo na výše uvedeném příkladu! Najdeme název DB pomocí dotazu:

SELECT 
    name 
FROM sys.databases 
WHERE database_id=6;
GO

V mém případě je to stále to samé databáze WideWorldImporters.

2.2) Dekódujeme hobt_id

V kontextu nalezené DB je potřeba provést dotaz na sys.partitions s pár joiny, které pomohou určit názvy tabulky a indexu…

USE WideWorldImporters;
GO
SELECT 
    sc.name as schema_name, 
    so.name as object_name, 
    si.name as index_name
FROM sys.partitions AS p
JOIN sys.objects as so on 
    p.object_id=so.object_id
JOIN sys.indexes as si on 
    p.index_id=si.index_id and 
    p.object_id=si.object_id
JOIN sys.schemas AS sc on 
    so.schema_id=sc.schema_id
WHERE hobt_id = 72057594041991168;
GO

Říká mi, že dotaz čekal na zablokování Application.Countries, používající index PK_Application_Countries.

2.3) Teď trochu magie %%lockres%% — pokud chcete zjistit, který záznam byl zablokován

Pokud opravdu chci zjistit, na kterém řádku bylo zablokování potřeba, mohu to zjistit pomocí dotazu na samotnou tabulku. Můžeme použít nedokumentovanou funkci %%lockres%% k nalezení záznamu odpovídajícího kouzelnému hash.
Mějte na paměti, že tento dotaz prohledá celou tabulku, a na velkých tabulkách to může být docela nepříjemné:

SELECT
    *
FROM Application.Countries (NOLOCK)
WHERE %%lockres%% = '(ce52f92a058c)';
GO

Přidala jsem NOLOCK (na radu Klause Aschenbrennera na Twitteru) protože zablokování může být problém. Chceme se jen podívat, co tam teď je, a ne co tam bylo, když transakce začala — nemyslím, že nám záleží na konzistenci dat.
A je to, záznam, o který jsme usilovali!

Poděkování a další čtení

Nepamatuji si, kdo poprvé popsal mnoho z těchto věcí, ale zde jsou dva příspěvky o nejméně zdokumentovaných věcech, které by vás mohly zajímat:

Příspěvek Paula Randal o %%physloc%% a sys.fn_PhysLocFormatter (jak jsme měli data v prvním příkladu)
Otázka na StackOverflow o použití %%lockres%% (jak jsme našli data ve druhém příkladu). Jeden z odpovědí odkazuje na příspěvek Granta Fritchea o %%lockres%%, napsaný už v roce 2010.

Zdroj: habr.com