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

Dacă utilizați raportul de blocări (blocked process report) sau colectați grafice de deadlock oferite de SQL Server, ocazional veți întâlni astfel de lucruri:

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

Uneori, în acel XML gigantic pe care îl examinați, va exista mai multă informație (graficele de deadlock conțin o listă a resurselor care ajută la identificarea numelui obiectului și indexului), dar nu întotdeauna.

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

Toate informațiile de aici sunt disponibile pe internet în diverse locuri, sunt doar foarte dispersate! Vreau să adun totul la un loc — de la DBCC PAGE la hobt_id și la funcțiile nedocumentate %%physloc%% și %%lockres%%.

În primul rând, să discutăm despre așteptările pe 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 oferi 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 o Bază de date WideWorldImporters pe SQL Server-ul meu.

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 pur și simplu să treceți la pasul următor, dar dacă sunteți interesat de numele fișierului, îl puteți găsi efectuând o interogare în contextul bazei de date găsite, introducând 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 la mine în C:MSSQLDATAWideWorldImporters_UserData.ndf. (Ups, m-a prins cum îmi plasez fișierele pe disk cu sistemul! Nu! A ieșit ciudat).

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 folosind DBCC PAGE nedocumentat și trace flag 3604.
Notă: prefer să folosesc DBCC PAGE pe o copie restaurată dintr-un backup undeva pe un alt server, deoarece este o chestie nedocumentată. În unele cazuri, aceasta poate duce la crearea unui dump (observație a traducătorului — linkul, din păcate, duce nicăieri, dar se pare că este vorba despre 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

Derulând la rezultate, puteți găsi object_id și index_id.

Aproape gata! Acum poți găsi numele tabelului și al 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ă observăm că a existat o așteptare pe blocare pe indexul PK_Sales_OrderLines din tabela Sales.OrderLines.

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

1.4) Poate putem vedea datele de pe pagina care a fost blocată?

Hmm, da. Dar… ești sigur că ai nevoie de asta?
Este lent chiar și pe tabele mici. Dar pare interesant, așa că, având în vedere că ai citit până aici… haide să vorbim despre %%physloc%%!

%%physloc%% este un mic ingredient magic nedocumentat care returnează identificatorul fizic pentru fiecare înregistrare. Poți folosi %%physloc%% împreună cu sys.fn_PhysLocFormatter în SQL Server 2008 și versiuni ulterioare.

Acum, când știm că am dorit să aplicăm o blocare pe pagina din Sales.OrderLines, putem vedea toate datele din această tabelă, care sunt stocate în fișierul de date #3 pe pagina #70133, folosind această 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 spus — este lent chiar și pe tabele foarte mici. Am adăugat NOLOCK la interogare pentru că oricum nu avem nicio garanție că datele pe care dorim să le vedem sunt exact aceleași ca cele care erau în momentul în care s-a descoperit blocarea — așa că putem face citiri murdare fără probleme.
Dar, urez, interogarea îmi returnează acele 25 de rânduri pentru care interogarea noastră s-a luptat.

Suficient despre blocările PAGE. Ce se întâmplă dacă așteptăm o blocare pe KEY?

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

Dacă interogarea ta încearcă să aplice o blocare pe o înregistrare din index și se blochează, primești un alt tip de adresă.
Împărțind “6:72057594041991168 (ce52f92a058c)” în părți, obținem:

database_id = 6
hobt_id = 72057594041991168
hashul 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 interogarea:

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

În cazul meu — este tot aceeași Bază de date WideWorldImporters.

2.2) Decodificăm hobt_id

În contextul Bazei de Date găsite, trebuie să executăm o interogare pe sys.partitions cu o pereche de join-uri care ne vor ajuta să determinăm numele 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ă vrei să afli care înregistrare a fost blocată

Dacă vreau să știu exact pe ce linie era necesară blocarea, pot afla asta folosind o interogare pe tabelul în sine. Putem utiliza 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 întregul tabel, iar pe tabele mari poate fi destul de neplăcut:

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

Am adăugat NOLOCK (după sfatul lui Klaus Aschenbrenner pe Twitter) deoarece blocările pot deveni o problemă. Vrem doar să vedem ce este acum, nu ce a fost atunci când a început tranzacția — nu cred că consistența datelor este importantă pentru noi.
Voilà, înregistrarea pentru care ne-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 aspecte care ar putea să vă placă:

Postarea lui Paul Randal despre %%physloc%% și sys.fn_PhysLocFormatter (așa cum am făcut cu datele noastre în primul exemplu)
Întrebarea pe StackOverflow despre utilizarea %%lockres%% (așa cum am găsit datele în al doilea exemplu). Una dintre răspunsuri duce la postarea lui Grant Fritchey despre %%lockres%%, scrisă încă din 2010.

Sursa: habr.com