🥇A kulcs és az oldalváró erőforrás megfejtése holtpontokban és zárolásokban

Ha a blokkolt folyamatjelentést használja, vagy rendszeres időközönként összegyűjti az SQL Server által biztosított holtpontdiagramokat, a következő dolgokkal találkozhat:

waitresource="PAGE: 6:3:70133"

waitresource=“KEY: 6:72057594041991168 (ce52f92a058c)“

Néha több információ is található abban az óriási XML-ben, amelyet tanulmányoz (a holtpont gráfok tartalmaznak egy olyan erőforráslistát, amely segít megtalálni az objektum és az index nevét), de nem mindig.

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

Az itt található összes információ különböző helyeken megtalálható az interneten, egyszerűen nagyon el van terjesztve! Mindent össze akarok rakni, a DBCC PAGE-tól a hobt_id-n át a nem dokumentált %%physloc%% és %%lockres%% függvényekig.

Először beszéljünk az OLDALzárak várakozásairól, majd térjünk át a KULCS zárakra.

1) waitresource="PAGE: 6:3:70133" = Adatbázis_azonosító: Fájlazonosító: Oldalszám

Ha kérése PAGE zárolásra vár, az SQL Server megadja az oldal címét.

Az "OLDAL: 6:3:70133" lebontásával a következőket kapjuk:

adatbázis_azonosító = 6
data_file_id = 3
oldalszám = 70133

1.1) Adatbázis_azonosító visszafejtése

Keresse meg az adatbázis nevét a lekérdezéssel:

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

Nyilvános DB WideWorldImporters az SQL szerveremen.

1.2) Az adatfájl nevének keresése - ha érdekel

A következő lépésben a data_file_id paramétert fogjuk használni a tábla nevének megtalálásához. Csak ugorhat a következő lépésre, de ha érdekli a fájl neve, megtalálhatja úgy, hogy a talált adatbázis kontextusában lekérdezést futtat, és a lekérdezésbe behelyettesíti a data_file_id paramétert:

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

A WideWorldImporters adatbázisban ez egy WWI_UserData nevű fájl, és visszaállítottam a C:MSSQLDATAWideWorldImporters_UserData.ndf fájlba. (Hoppá, rajtakapott, hogy fájlokat teszek a rendszermeghajtóra! Nem! Kínos).

1.3) Az objektumnév lekérése a DBCC PAGE-ról

Ma már tudjuk, hogy a 70133. adatfájl #3. oldala a WorldWideImporters adatbázishoz tartozik. Ennek az oldalnak a tartalmát a nem dokumentált DBCC PAGE és a 3604 nyomkövetési jelző használatával tekinthetjük meg.
Megjegyzés: Inkább használom a DBCC PAGE-t egy visszaállított példányon valahol egy másik szerveren, mert ez nem dokumentált cucc. Bizonyos esetekben ő szemétlerakást eredményezhet (kb. fordító - a link sajnos nem vezet sehova, de az url alapján szűrt indexekről beszélünk).

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

Az eredményekhez görgetve megtalálhatja az objektum_azonosítóját és az index_idjét.

Majdnem kész! Most egy lekérdezéssel megtalálhatja a tábla- és indexneveket:

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 itt látjuk, hogy a zárolási várakozás a Sales.OrderLines tábla PK_Sales_OrderLines indexén volt.

Megjegyzés: Az SQL Server 2014 és újabb verzióiban az objektumnév a nem dokumentált DMO sys.dm_db_database_page_allocations használatával is megtalálható. De az adatbázisban minden oldalt le kell kérdezni, ami nagy adatbázisoknál nem tűnik túl menőnek, ezért a DBCC PAGE-t használtam.

1.4) Megtekinthetők az adatok a letiltott oldalon?

Nuuu, igen. De... biztos vagy benne, hogy tényleg szükséged van rá?
Még kis asztalokon is lassú. De ez nagyon klassz, szóval mivel idáig olvastál... beszéljünk a %%physloc%%-ról!

A %%physloc%% egy dokumentálatlan varázslat, amely minden bejegyzéshez fizikai azonosítót ad vissza. Te tudod használni %%physloc%% a sys.fn_PhysLocFormatterrel együtt az SQL Server 2008 és újabb verzióiban.

Most, hogy tudjuk, hogy blokkolni akartuk az oldalt a Sales.OrderLines-ben, a következő lekérdezéssel megnézhetjük ennek a táblázatnak az összes adatát, amely a #3. adatfájlban (#70133. oldalon) van tárolva:

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

Mint mondtam, még apró asztalokon is lassú. A NOLOCK-ot azért adtam hozzá a kéréshez, mert továbbra sincs garanciánk arra, hogy az általunk vizsgált adatok pontosan megegyeznek a zár felfedezésének időpontjában lévő adatokkal – így nyugodtan végezhetünk piszkos leolvasásokat.
De hurrá, a lekérdezés pontosan azt a 25 sort adja vissza, amiért a lekérdezésünk harcolt

Elég az OLDALzárakról. Mi van, ha KEY-zárra várunk?

2) waitresource="KEY: 6:72057594041991168 (ce52f92a058c)" = Database_Id, HOBT_Id (mágikus kivonat, amely %%lockres%%-kal visszafejthető, ha igazán akarja)

Ha a lekérdezés megpróbál zárolni egy indexbejegyzést, és önmagában zárolódik, akkor teljesen más típusú címet kap.
A „6:72057594041991168 (ce52f92a058c)”-t részekre bontva a következőket kapjuk:

adatbázis_azonosító = 6
hobt_id = 72057594041991168
mágikus hash = (ce52f92a058c)

2.1) Adatbázis_azonosító visszafejtése

Pontosan ugyanúgy működik, mint a fenti példánál! Az adatbázis nevét a következő lekérdezéssel találjuk meg:

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

Az én esetemben ugyanez DB WideWorldImporters.

2.2) A hobt_id visszafejtése

A talált adatbázis keretében le kell kérdeznie a sys.partitions-t néhány csatlakozással, amelyek segítenek meghatározni a tábla é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, hogy a kérés az Application.Countries záron várakozott a PK_Application_Countries index használatával.

2.3) Most néhány %%lockres%% varázslat – ha meg akarod tudni, melyik bejegyzés volt zárolva

Ha nagyon szeretném tudni, hogy melyik sorban volt szükség a zárra, akkor magának a táblázatnak a lekérdezésével megtudhatom. A nem dokumentált %%lockres%% függvény segítségével megtalálhatjuk a mágikus hash-nek megfelelő bejegyzést.
Vegye figyelembe, hogy ez a lekérdezés a teljes táblázatot átvizsgálja, és nagy táblákon ez egyáltalán nem lehet szórakoztató:

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

hozzáadtam a NOLOCK-ot (Klaus Aschenbrenner tanácsára a twitteren), mert a zárak gondot okozhatnak. Csak azt akarjuk megnézni, hogy most mi van, és nem azt, hogy mi volt ott a tranzakció kezdetekor – nem hiszem, hogy az adatok konzisztenciája fontos számunkra.
Voilá, a rekord, amiért harcoltunk!

Köszönetnyilvánítás és további olvasmányok

Nem emlékszem, ki írta le először ezeket a dolgokat, de itt van két bejegyzés a legkevésbé dokumentált dolgokról, amelyek tetszhetnek:

Paul Randal bejegyzése kb %%physloc%% és sys.fn_PhysLocFormatter (ahogy az első példában adataink vannak)
Kérdés a StackOverflow-ról %%lockres%% használatával (ahogy a második példában találtuk az adatokat). Az egyik válasz a bejegyzéshez vezet Grant Fritchey 2010-ben írt %%lockres%%-ról.

Forrás: will.com

A kulcs és az oldalváró erőforrás megfejtése holtpontokban és zárolásokban