Dacă utilizați raportul de proces blocat sau colectați periodic graficele de blocaj furnizate de SQL Server, veți întâlni astfel de lucruri:
waitresource=“PAGINA: 6:3:70133“
waitresource=“KEY: 6:72057594041991168 (ce52f92a058c)“
Uneori vor exista mai multe informații în acel XML gigant pe care îl studiați (graficele de blocaj conțin o listă de resurse care vă ajută să aflați numele obiectelor și indexului), dar nu întotdeauna.
Acest text vă va ajuta să le descifrați.
Toate informațiile care sunt aici sunt pe Internet în diverse locuri, doar că sunt foarte distribuite! Vreau să pun totul împreună - de la DBCC PAGE la hobt_id și la funcțiile nedocumentate %%physloc%% și %%lockres%%.
Mai întâi, să vorbim 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 dvs. așteaptă o blocare PAGE, SQL Server vă va oferi adresa acelei pagini.
Defalcând „PAGE: 6:3:70133” obținem:
- database_id = 6
- data_file_id = 3
- page_numer = 70133
1.1) Decriptați database_id
Să găsim numele bazei de date folosind interogarea:
SELECT
name
FROM sys.databases
WHERE database_id=6;
GOAcesta este public pe serverul meu SQL.
1.2) Căutați 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 sări pur și simplu la pasul următor, dar dacă sunteți interesat de numele fișierului, îl puteți găsi rulând o interogare în contextul bazei de date găsite, înlocuind 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 l-am restaurat în C:MSSQLDATAWideWorldImporters_UserData.ndf. (Hopa, m-ai prins punând fișiere pe discul de sistem! Nu! A fost ciudat).
1.3) Obțineți numele obiectului din DBCC PAGE
Acum știm că pagina #70133 din fișierul de date 3 aparține bazei de date WorldWideImporters. Putem privi conținutul acestei pagini folosind PAGINA DBCC nedocumentată și semnalul de urmărire 3604.
Notă: prefer să folosesc DBCC PAGE pe o copie restaurată dintr-o copie de rezervă undeva pe alt server, deoarece este un lucru nedocumentat. În unele cazuri, ea (aproximativ traducător - linkul, din păcate, nu duce nicăieri, dar judecând după url, vorbim de indici 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 rezultatele, puteți găsi object_id și index_id.
Aproape gata! Acum puteți găsi numele de tabel și index 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 acum vedem că așteptarea de 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 folosind DMO nedocumentat sys.dm_db_database_page_allocations. Dar trebuie să interogați fiecare pagină din baza de date, ceea ce nu arată foarte bine pentru bazele de date mari, așa că am folosit DBCC PAGE.
1.4) Este posibil să vedeți datele de pe pagina care a fost blocată?
Ei bine, da. Dar... ești sigur că chiar ai nevoie de el?
Este lent chiar și pe mese mici. Dar e cam misto, așa că de când ai citit până aici... hai să vorbim despre %%physloc%%!
%%physloc%% este o piesă magică nedocumentată care returnează un identificator fizic pentru fiecare intrare. poți să folosești .
Acum că știm că am dorit să blocăm pagina în Sales.OrderLines, putem analiza toate datele din acest tabel, care sunt stocate în fișierul de date #3 de 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%'
GODupă cum am spus, este lent chiar și pe mese mici. Am adăugat NOLOCK la cerere, deoarece încă nu avem nicio garanție că datele pe care vrem să le analizăm sunt exact aceleași ca și când a fost detectată blocarea - astfel încât să putem face citiri murdare în siguranță.
Dar, ură, interogarea îmi returnează aceleași 25 de rânduri pentru care a luptat interogarea noastră
Destul despre blocările PAGE. Ce se întâmplă dacă așteptăm o încuietoare cu CHEIE?
2) waitresource=“KEY: 6:72057594041991168 (ce52f92a058c)” = Database_Id, HOBT_Id (hash magic care poate fi decriptat folosind %%lockres%% dacă chiar doriți asta)
Dacă interogarea dvs. încearcă să blocheze o înregistrare în index și se blochează singură, veți ajunge cu un tip complet diferit de adresă.
Împărțind „6:72057594041991168 (ce52f92a058c)” în părți, obținem:
- database_id = 6
- hobt_id = 72057594041991168
- hash magic = (ce52f92a058c)
2.1) Decriptați database_id
Funcționează exact la fel ca exemplul de mai sus! Găsiți numele bazei de date folosind interogarea:
SELECT
name
FROM sys.databases
WHERE database_id=6;
GOIn cazul meu e tot la fel .
2.2) Decriptați hobt_id
În contextul bazei de date găsite, trebuie să executați o interogare la sys.partitions cu o pereche de îmbinări care va ajuta la determinarea numelor tabelului și 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ă cererea așteaptă pe aplicația. Blocarea țărilor folosind indexul PK_Application_Countries.
2.3) Acum puțină magie %%lockres%% - dacă doriți să aflați ce intrare a fost blocată
Dacă vreau cu adevărat să știu pe ce rând era blocarea, pot afla interogând tabelul în sine. Putem folosi funcția nedocumentată %%lockres%% pentru a găsi o intrare care se potrivește cu hashul magic.
Vă rugăm să rețineți că această interogare va scana întregul tabel, iar pe tabele mari acest lucru poate să nu fie deloc distractiv:
SELECT
*
FROM Application.Countries (NOLOCK)
WHERE %%lockres%% = '(ce52f92a058c)';
GO
Am adăugat NOLOCK () deoarece blocajele pot deveni o problemă. Vrem doar să ne uităm la ce este acolo acum și nu la ce era acolo când a început tranzacția - nu cred că consecvența datelor este importantă pentru noi.
Voila, recordul pentru care am luptat!
Mulțumiri și lecturi suplimentare
Nu-mi amintesc cine a descris primul multe dintre aceste lucruri, dar iată două postări despre cele mai puțin documentate lucruri care ți-ar putea plăcea:
- Postarea lui Paul Randal despre (cum avem datele noastre în primul exemplu)
- Întrebare pe StackOverflow despre (după cum am găsit datele în al doilea exemplu). Unul dintre răspunsuri duce la o postare .
Sursa: www.habr.com