Dekodowanie Key i Page WaitResource dla zakleszczeń i blokad

W trakcie analizowania blokowań w SQL Server można często spotkać się z wpisami w XML:

waitresource=“PAGE: 7:4:50256 "

lub

waitresource=“KEY: 8:72046656561991169 (cd54f92b253d)”

Co to oznacza ? Jak to przetłumaczyć ?

Pokrótce blokada PAGE występuje na poziomie strony, a KEY na poziomie Klucza no ale po kolei.

Page lock waits

Jeśli mamy wpis

waitresource=“ PAGE: 7:4:50256"

oznacza to, że zapytanie czekało na blokadę na poziomie strony. Silnik podaje nam po kolei

PAGE: 7:4:50256

• PAGE – blokada na poziomie strony
• 7 – jest to database_id czyli identyfikator bazy danych
• 4 – data_file_id – identyfikator pliku bazy danych
• 50256 – numer strony danych

W celu sprawdzenia jaka to baza o identyfikatorze 7 musimy użyć skryptu:

SELECT name 
FROM sys.databases 
WHERE database_id=7

gdzie uzyskamy nazwę bazy: DBA.

Teraz można znając już nazwę bazy można uzyskać nazwę pliku bazy danych.

USE DBA
SELECT name, physical_name
FROM sys.database_files
WHERE file_id = 4

W odpowiedzi dostajemy:

• Name: DBA_Data
• Physical_name:  H:\MSSQL\DATA\DBA_Data.ndf

Używając DBCC PAGE możemy odnaleźć identyfikator obiektu

/* 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 ('DBA',4, 50256,2);

Oglądając zrzut można odnaleźć ObcjectID oraz IndexID i za pomocą poniższego zapytania odszukujemy nazwę tabeli i indeksu.

USE DBA
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 = 92728981
    and si.index_id = 1;
GO

Tym samym możemy wyczytać na jakiej tabeli i jakim indeksie była blokada.

Key lock waits

Analogicznie gdy blokada występuje na poziomie klucza możemy rozkodować wpis

Przykładowo mamy

waitresource=“KEY: 8:72046656561991169 (cd54f92b253d)”

• Identyfikator bazy – 8
• Hobt_id = 72046656561991169
• Wartość skrótu (cd54f92b253d)

Analogicznie jak poprzednio odnajdujemy nazwę bazy danych:

SELECT name
FROM sys.databases
WHERE database_id=8

Uzyskujemy np. DBA_Monitor

Hobt_id wyciągamy z sys.partitions

USE DBAMonitor
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 = 72046656561991169;

I można teraz wyciągnąć informację, że zapytanie było zablokowane przez tabelę cfg.Drivers a użyty był indeks PK_Drivers

Znając już nazwę tabeli można za pomocą nieudokumentowanego %%lockres%% znaleźć rekord , który spowodował blokadę.

SELECT  *
FROM cfg.Drivers (NOLOCK)
WHERE %%lockres%% = '(cd54f92b253d)';