🥇Rozšifrujeme Key a Page WaitResource v deadlockoch a zablokovaniach

Ak používate správu o blokovaných procesoch (blocked process report) alebo zbierate grafy deadlockov poskytované SQL Serverom, občas narazíte na takéto veci:

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

Niekedy v obrovskom XML, ktoré skúmate, bude viac informácií (grafy deadlockov obsahujú zoznam zdrojov, ktorý pomáha zistiť názvy objektov a indexov), ale nie vždy.

Tento text vám pomôže ich rozlúštiť.

Všetky informácie, ktoré tu sú, sú na internete na rôznych miestach, sú jednoducho veľmi roztrúsené! Chcem ich zhromaždiť dokopy – od DBCC PAGE po hobt_id a nedokumentované %%physloc%% a %%lockres%% funkcie.

Najprv sa porozprávajme o čakaniach na PAGE-zamknutiach a potom prejdeme k KEY-zamknutiam.

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

Ak váš dotaz čaká na PAGE-zamknutí, SQL Server vám poskytne adresu tejto stránky.

Rozložením „PAGE: 6:3:70133“ dostaneme:

database_id = 6
data_file_id = 3
page_number = 70133

1.1) Rozlúšťujeme database_id

Nájdeme názov databázy pomocou dotazu:

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

Toto je verejná DB WideWorldImporters na mojom SQL Serveri.

1.2) Hľadáme názov súboru dát — ak vás to zaujíma

Chystáme sa použiť data_file_id v nasledujúcom kroku na nájdenie názvu tabuľky. Môžete jednoducho prejsť na ďalší krok, ale ak vás zaujíma názov súboru, môžete ho nájsť vykonaním dotazu v kontexte nájdenej databázy, pričom do tohto dotazu vložíte data_file_id:

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

V DB WideWorldImporters je tento súbor nazvaný WWI_UserData a je obnovený u mňa na C:MSSQLDATAWideWorldImporters_UserData.ndf. (Ups, prichytili ste ma, ako ukladám súbory na disk so systémom! Nie! To bola nepríjemná situácia).

1.3) Získavame názov objektu z DBCC PAGE

Teraz vieme, že stránka #70133 v súbore dát 3 patrí k DB WorldWideImporters. Môžeme sa pozrieť na obsah tejto stránky pomocou nedokumentovaného DBCC PAGE a trace-flagu 3604.
Poznámka: uprednostňujem používanie DBCC PAGE na obnovených kópiách z backupu niekde na inom serveri, pretože je to nedokumentovaná funkcia. V niektorých prípadoch to môže viesť k vytvoreniu dumpu (poznámka prekladateľa — odkaz, žiaľ, vedie nikam, ale podľa url sa hovorí o filtered-indexoch).

/* 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

Prejdením k výsledkom môžete nájsť object_id a index_id.

Takmer hotovo! Teraz môžeme nájsť mená tabuľky a indexu pomocou 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 teraz vidíme, že čakacia doba na blokovaní bola na indexe PK_Sales_OrderLines tabuľky Sales.OrderLines.

Poznámka: v SQL Server 2014 a vyššie možno názov objektu nájsť aj pomocou nedokumentovaného DMO sys.dm_db_database_page_allocations. Ale budete musieť dotazovať každú stránku v DB, čo nevyzerá veľmi dobre pre veľké databázy, preto som použila DBCC PAGE.

1.4) Môžeme vidieť dáta na tej stránke, ktorá bola zablokovaná?

No, áno. Ale… ste si istí, že to naozaj potrebujete?
Je to pomalé aj na malých tabuľkách. Ale je to akosi zaujímavé, takže keď ste dočítali až sem… poďme sa porozprávať o %%physloc%%!

%%physloc%% je nedokumentovaný kúsok mágie, ktorý vracia fyzický identifikátor pre každý záznam. Môžete ho použiť %%physloc%% spolu so sys.fn_PhysLocFormatter v SQL Server 2008 a vyššie.

Teraz, keď vieme, že sme chceli uplatniť blokovanie na stránku v Sales.OrderLines, môžeme sa pozrieť na všetky dáta v tejto tabuľke, ktoré sú uložené v súbore údajov #3 na stránke #70133, pomocou takéhoto dotazu:

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

Ako som povedala — je to pomalé aj na malých tabuľkách. Pridala som k dotazu NOLOCK, pretože aj tak nemáme záruky, že dáta, na ktoré sa chceme pozrieť, sú presne tie, ktoré boli v momente, keď bola zistená blokáda — takže sa môžeme pokojne zaoberať špinavým čítaním.
Ale hurá, dotaz mi vracia tých 25 riadkov, za ktoré náš dotaz bojoval.

Dosť o PAGE blokovaniach. Čo ak čakáme na blokovanie KEY?

2) waitresource="KEY: 6:72057594041991168 (ce52f92a058c)" = Database_Id, HOBT_Id (magický hash, ktorý sa dá dešifrovať pomocou %%lockres%%, ak to naozaj chcete)

Ak sa váš dotaz snaží uplatniť blokovanie na záznam v indexe a je sám zablokovaný, dostanete úplne iný typ adresy.
Rozdelením „6:72057594041991168 (ce52f92a058c)“ na časti dostaneme:

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

2.1) Dešifrujeme database_id

Funguje to presne tak, ako v predchádzajúcom príklade! Zistíme názov DB pomocou dopytu:

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

V mojom prípade je to stále tá istá DB WideWorldImporters.

2.2) Rozšifrujeme hobt_id

V kontexte nájdenej DB je potrebné vykonať dopyt k sys.partitions s dvojicou joinov, ktoré pomôžu určiť názvy tabuľky 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

Hovorí mi, že dopyt čakal na zablokovanie Application.Countries, používajúc index PK_Application_Countries.

2.3) Teraz trochu mágie %%lockres%% — ak chcete zistiť, ktorá záznam bola zablokovaná

Ak naozaj chcem vedieť, na ktorom riadku bola potrebná zablokovanie, môžem to zistiť pomocou dopytu k samotnej tabuľke. Môžeme využiť nedokumentovanú funkciu %%lockres%% na nájdenie záznamu zodpovedajúceho magickému hash.
Majte na pamäti, že tento dopyt prehľadá celú tabuľku a na veľkých tabuľkách to nemusí byť vôbec zábava:

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

Pridala som NOLOCK (podľa rady Klausa Aschenbrennera na Twitteri) pretože zablokovania môžu byť problémom. Chceme sa len pozrieť, čo tam teraz je, a nie čo tam bolo, keď sa transakcia začala — nemyslím si, že konzistencia údajov je pre nás dôležitá.
Voila, záznam, o ktorý sme bojovali!

Poďakovania a ďalšie čítanie

Nepamätám si, kto prvý popísal mnohé z týchto vecí, ale tu sú dva príspevky o najmenej dokumentovaných veciach, ktoré by sa vám mohli páčiť:

Príspevok Paula Randal o %%physloc%% a sys.fn_PhysLocFormatter (ako sme spracovali naše údaje v prvom príklade)
Otázka na StackOverflow o používaní %%lockres%% (ako sme našli údaje v druhom príklade). Jeden z odpovedí vedie na príspevok Granta Fritcheyho o %%lockres%%, napísaný už v 2010.

Zdroj: habr.com