🥇Decodificăm Key și Page WaitResource în deadlock-uri și blocaje

Dacă utilizați raportul de blocare (blocked process report) sau colectați graficele de deadlock oferite de SQL Server, periodic veți întâlni lucruri de genul acesta:

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

Uneori, în acel XML uriaș pe care îl studiați, va exista mai multe informații (graficele de deadlock conțin o listă de resurse care ajută la identificarea numelui obiectului și indexului), dar nu întotdeauna.

Acest text vă va ajuta să le descifrați.

Toate informațiile disponibile aici sunt disponibile pe internet în diferite locuri, sunt doar foarte distribuite! Vreau să le adun pe toate împreună — de la DBCC PAGE la hobt_id și funcțiile nedocumentate %%physloc%% și %%lockres%%.

Mai întâi, să discutăm despre așteptările la blocările PAGE, apoi vom trece la blocările KEY.

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

Dacă interogarea dumneavoastră așteaptă pe o blocare PAGE, SQL Server vă va da adresa acestei pagini.

Descompunând „PAGE: 6:3:70133” obținem:

database_id = 6
data_file_id = 3
page_number = 70133

1.1) Descifrăm database_id

Vom găsi numele bazei de date folosind interogarea:

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

Aceasta este baza de date publică WideWorldImporters pe serverul meu SQL.

1.2) Căutăm numele fișierului de date — dacă sunteți interesat

Vom folosi data_file_id în următorul pas pentru a găsi numele tabelului. Puteți trece direct la următorul pas, dar dacă sunteți interesat de numele fișierului, îl puteți găsi executând o interogare în contextul bazei de date găsite, substituind data_file_id în această interogare:

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

În baza de date WideWorldImporters, acesta este un fișier numit WWI_UserData și este restaurat pe C:MSSQLDATAWideWorldImporters_UserData.ndf. (Ups, m-ați prins că bag fișiere pe disc cu sistemul! Nu! A fost stânjenitor).

1.3) Obținem numele obiectului din DBCC PAGE

Acum știm că pagina #70133 din fișierul de date 3 aparține bazei de date WorldWideImporters. Putem vizualiza conținutul acestei pagini utilizând DBCC PAGE nedocumentat și trace flag 3604.
Notă: Prefer să folosesc DBCC PAGE pe o copie restaurată din backup pe un alt server, deoarece aceasta este o funcție nedocumentată. În unele cazuri, aceasta poate conduce la crearea unui dump (notă a traducătorului — din păcate, linkul duce nicăieri, dar conform url-ului, se referă la indecși filtrați.).

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

Dând scroll la rezultate, se poate găsi object_id și index_id.

Gata aproape! Acum putem găsi numele tabelului și indexului folosind interogarea:

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

Și iată că vedem că așteptarea pe blocare a fost pe indexul PK_Sales_OrderLines al tabelului Sales.OrderLines.

Notă: în SQL Server 2014 și versiuni ulterioare, numele obiectului poate fi găsit și prin DMO nedocumentat sys.dm_db_database_page_allocations. Dar va trebui să solicitați fiecare pagină din baza de date, ceea ce nu arată prea elegant pentru baze de date mari, așa că am folosit DBCC PAGE.

1.4) Dar se poate vedea datele de pe pagina care a fost blocată?

Bine, da. Dar… sunteți sigur că aveți nevoie de asta?
Este lent chiar și pe tabele mici. Dar este oarecum interesant, așa că, dacă ați citit până aici… să vorbim despre %%physloc%%!

%%physloc%% este un fragment de magie nedocumentat care returnează identificatorul fizic pentru fiecare înregistrare. Puteți folosi %%physloc%% împreună cu sys.fn_PhysLocFormatter în SQL Server 2008 și versiuni ulterioare.

Acum, când știm că am vrut să aplicăm o blocare pe pagina din Sales.OrderLines, putem vizualiza toate datele din acest tabel, care sunt stocate în fișierul de date #3 pe pagina #70133, folosind următoarea interogare:

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

Așa cum am menționat — este lent chiar și pe tabele mici. Am adăugat NOLOCK la interogare pentru că oricum nu avem nicio garanție că datele pe care vrem să le vedem sunt exact aceleași pe când s-a descoperit blocarea — așa că putem face citiri murdare fără probleme.
Dar, urmează, interogarea îmi returnează exact cele 25 de rânduri pentru care interogarea noastră a luptat

Să vorbim despre blocările PAGE. Ce s-ar întâmpla dacă așteptăm o blocare KEY?

2) waitresource=“KEY: 6:72057594041991168 (ce52f92a058c)” = Database_Id, HOBT_Id (hash-ul magic, care poate fi decriptat cu %%lockres%%, dacă chiar vrei asta)

Dacă interogarea ta încearcă să aplice o blocare pe o înregistrare din index și ajunge să fie blocată, obții un alt tip de adresă.
Despicând “6:72057594041991168 (ce52f92a058c)” în părți, obținem:

database_id = 6
hobt_id = 72057594041991168
hash-ul magic = (ce52f92a058c)

2.1) Decriptăm database_id

Funcționează exact la fel ca în exemplul de mai sus! Găsim numele Bazei de Date folosind o interogare:

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

În cazul meu, este tot aceeași WideWorldImporters.

2.2) Decodăm hobt_id

În contextul bazei de date găsite, trebuie să executăm o interogare la sys.partitions cu o pereche de join-uri, care vor ajuta la determinarea numelui tabelului și al indexului…

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

Îmi spune că interogarea a așteptat pe blocarea Application.Countries, folosind indexul PK_Application_Countries.

2.3) Acum puțină magie %%lockres%% — dacă doriți să descoperiți ce înregistrare a fost blocată

Dacă vreau cu adevărat să aflu pe ce rând a fost necesară blocarea, pot descoperi acest lucru printr-o interogare la tabelul în sine. Putem folosi funcția nedocumentată %%lockres%% pentru a găsi înregistrarea care se potrivește cu hash-ul magic.
Rețineți că această interogare va scana întreg tabelul, și pe tabele mari, acest lucru poate fi destul de problematic:

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

Am adăugat NOLOCK (la sugestia lui Klaus Aschenbrenner pe Twitter) pentru că blocările pot deveni o problemă. Vrem doar să vedem ce este acolo acum, nu ce a fost când a început tranzacția — nu cred că ne interesează consistența datelor.
Uite, înregistrarea pentru care am luptat!

Mulțumiri și lecturi suplimentare

Nu-mi amintesc cine a descris prima dată multe dintre aceste lucruri, dar iată două postări despre cele mai puțin documentate tehnici, care s-ar putea să-ți placă:

Postarea lui Paul Randal despre %%physloc%% și sys.fn_PhysLocFormatter (așa cum am obținut datele noastre în primul exemplu)
O întrebare pe StackOverflow despre utilizarea %%lockres%% (așa cum am găsit datele în al doilea exemplu). Unul dintre răspunsuri duce la postarea Grant Fritchey despre %%lockres%%, scrisă încă din 2010.

Sursa: habr.com