🥇A Key és Page WaitResource dekódolása holtpontokban és blokkolásokban

Ha blokkolási jelentést (blocked process report) használ, vagy időnként elemzi az SQL Server által nyújtott holtpont grafikonokat, ezekkel a dolgokkal fog találkozni:

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

Néha, abban a hatalmas XML-ben, amit elemez, több információt talál (a holtpont grafikonok tartalmazzák a források listáját, ami segít az objektum és index nevének felfedezésében), de nem mindig.

Ez a szöveg segít majd megfejteni őket.

Minden információ, ami itt található, elérhető az interneten különböző helyeken, csak nagyon szétszórt! Szeretném mindent összehozni — a DBCC PAGE-től kezdve a hobt_id-ig és a nem dokumentált %%physloc%% és %%lockres%% funkciókig.

Először beszéljünk a PAGE zárolásokkal kapcsolatos várakozásokról, majd áttérünk a KEY zárolásokra.

1) waitresource="PAGE: 6:3:70133" = Adatbázis_Id: FájlId: Oldalszám

Ha a kérés PAGE zárolásra vár, az SQL Server megadja ennek az oldalnak a címét.

A „PAGE: 6:3:70133” elemzésével megkapjuk:

database_id = 6
data_file_id = 3
page_number = 70133

1.1) Fejtjük meg a database_id-t

Keresd meg az adatbázis nevét a lekérdezés segítségével:

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

Ez a nyilvános WideWorldImporters adatbázis az én SQL Serveremen.

1.2) Keresd meg az adatfájl nevét — ha érdekel

A következő lépésben a data_file_id-t használjuk, hogy megtaláljuk a táblázat nevét. Egyszerűen léphet a következő lépésre, de ha érdekel a fájl neve, akkor ezt a lekérdezést futtathatod a megtalált adatbázis kontextusában, a data_file_id-t behelyettesítve:

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

A WideWorldImporters adatbázisban ez a név WWI_UserData fájl, és a C:MSSQLDATAWideWorldImporters_UserData.ndf helyreállítottam. (Ups, rajtakaptál, ahogy a fájlokat a rendszerlemezre helyezem! Nem! Zavarba ejtő volt).

1.3) Az objektum nevét a DBCC PAGE-ből

Most már tudjuk, hogy a #70133 oldal a 3-as adatfájlban a WorldWideImporters adatbázishoz tartozik. Megnézhetjük ennek az oldalnak a tartalmát a nem dokumentált DBCC PAGE és a 3604-es nyomkód segítségével.
Megjegyzés: Preferálom a DBCC PAGE-t egy visszaállított példányon futtatni, amely egy másik szerveren van, mert ez egy nem dokumentált dolog. Bizonyos esetekben ez dömpingeléshez vezethet (fők. fordító — a hivatkozás sajnos semmire sem vezet, de a URL alapján szűrt indexekről van szó.).

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

A találatokhoz görgetve megtalálhatjuk az object_id-t és index_id-t.

Majdnem kész! Most már lekérdezéssel megtalálhatók a táblák és indexek nevei:

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

És láthatjuk, hogy a várakozás a blokkolás szempontjából a Sales.OrderLines tábla PK_Sales_OrderLines indexén volt.

Megjegyzés: SQL Server 2014-től kezdődően az objektum nevét egy dokumentálatlan DMO sys.dm_db_database_page_allocations segítségével is meg lehet találni. De minden egyes oldalt le kell kérdezni az adatbázisban, ami nem igazán menő nagy adatbázisok esetén, ezért én a DBCC PAGE-t használtam.

1.4) Meg lehet nézni azokat az adatokat, amelyek a blokkolt oldalon voltak?

Hát, igen. De... biztos, hogy erre tényleg szükség van?
Ez lassú még a kis táblák esetén is. De valamiért érdekes, így ha már eddig eljutottál... beszéljünk a %%physloc%%-ról!

A %%physloc%% egy dokumentálatlan varázslat, amely visszaadja a fizikai azonosítót minden egyes rekordhoz. Használhatod %%physloc%% a sys.fn_PhysLocFormatter-rel SQL Server 2008-tól kezdődően.

Most, hogy tudjuk, hogy a Sales.OrderLines táblában akartunk blokkolni egy oldalt, megnézhetjük a táblában található összes adatot, amelyek a #3 adatfájl #70133 oldalán találhatók, a következő lekérdezéssel:

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

Ahogy mondtam, ez lassú még a pici táblák esetén is. A lekérdezéshez NOLOCK-ot adtam, mert úgyis nincs garancia arra, hogy az adatok, amiket megnézni szeretnénk, pontosan azok, amelyek akkor voltak, amikor a blokkolás észlelve lett — szóval nyugodtan végezhetünk piszkos olvasásokat.
De, hurrá, a lekérdezés visszaadta nekem azokat a 25 sort, amelyekért a kérésünk harcolt.

Elég a PAGE-blokkolásokról. Mi van, ha KEY-blokkolást várunk?

2) waitresource="KEY: 6:72057594041991168 (ce52f92a058c)" = Database_Id, HOBT_Id (varázslatos hash, amelyet %%lockres%% segítségével dekódolhatsz, ha tényleg akarod)

Ha a lekérdezésed rekorderzékre próbál blokkolni és blokkolva van, egy teljesen más típusú címet kapsz.
A "6:72057594041991168 (ce52f92a058c)" bontásával azt kapjuk:

database_id = 6
hobt_id = 72057594041991168
varázslatos hash = (ce52f92a058c)

2.1) Dekódoljuk a database_id-t

Ez pontosan ugyanúgy működik, mint a fenti példában! Az adatbázis nevét a lekérdezés segítségével találjuk meg:

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

Az én esetemben ez még mindig az WideWorldImporters adatbázis.

2.2) Fejtsük vissza a hobt_id-t

A megtalált adatbázis kontextusában le kell futtatni egy lekérdezést a sys.partitions-hoz, egy pár összekapcsolással, amelyek segítenek meghatározni a táblázat és az index nevét...

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

Azt mondja nekem, hogy a lekérdezés az Application.Countries zárolására várt, a PK_Application_Countries indexet használva.

2.3) Most egy kis varázslat %%lockres%% — ha szeretnéd kideríteni, melyik rekord volt zárolva

Ha valóban szeretném tudni, melyik sorra volt szükség a zárolásnál, akkor ezt a táblázat legjobb lekérdezésével megtudhatom. Az %%lockres%% nem dokumentált funkciót használhatjuk, hogy megtaláljuk a rekordot, amely megegyezik a varázslatos hashel.
Ne feledd, hogy ez a lekérdezés végigmegy az egész táblázaton, és nagy táblák esetében ez nem biztos, hogy szórakoztató lesz:

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

Hozzáadtam a NOLOCK-at (Klaus Aschenbrenner Twitter javaslata alapján) mert a zárolások problémát okozhatnak. Csak azt akarjuk megnézni, mi van ott most, nem azt, ami akkor volt, amikor a tranzakció elkezdődött — nem hiszem, hogy az adatok konzisztenciája fontos nekünk.
Voilà, a rekord, amiért küzdöttünk!