🥇Dešifriramo Key i Page WaitResource u mrtvim blokadama i zaključavanjima

Ako koristite izvještaj o blokadama (blocked process report) ili okupljate grafikon mrtvih blokada koje pruža SQL Server, s vremena na vrijeme ćete se susresti s nečim poput ovoga:

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

Ponekad, u toj ogromnoj XML datoteci koju proučavate, bit će više informacija (grafikon mrtvih blokada sadrži popis resursa koji pomaže da se saznaju imena objekta i indeksa), ali ne uvijek.

Ovaj tekst će vam pomoći da ih protumačite.

Sve informacije koje ovdje postoje, dostupne su na internetu na raznim mjestima, samo su jako raspršene! Želim sve skupiti — od DBCC PAGE do hobt_id i nedokumentiranih %%physloc%% i %%lockres%% funkcija.

Prvo ćemo razgovarati o čekanjima na PAGE blokadama, a zatim ćemo preći na KEY blokade.

1) waitresource="PAGE: 6:3:70133" = Database_Id: FileId: PageNumber

Ako vaša upita čeka na PAGE blokadi, SQL Server će vam dati adresu te stranice.

Razlažući «PAGE: 6:3:70133», dobivamo:

database_id = 6
data_file_id = 3
page_number = 70133

1.1) Tumačimo database_id

Potražit ćemo ime baze podataka uz pomoć upita:

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

To je javno Baza podataka WideWorldImporters na mom SQL Serveru.

1.2) Tražimo ime datoteke podataka — ako vas zanima

Koristit ćemo data_file_id u sljedećem koraku kako bismo pronašli ime tablice. Možete jednostavno preći na sljedeći korak, ali ako vas zanima ime datoteke, možete ga pronaći izvršavanjem upita u kontekstu pronađene Baze podataka, umetnite data_file_id u ovaj upit:

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

U Bazi podataka WideWorldImporters, to je datoteka nazvana WWI_UserData i ona je obnovljena na mom C:MSSQLDATAWideWorldImporters_UserData.ndf. (Ups, uhvatili ste me kako stavljam datoteke na disk sa sustavom! Ne! Bilo je nezgodno).

1.3) Dobivamo ime objekta iz DBCC PAGE

Sada znamo da stranica #70133 u datoteci podataka 3 pripada Bazi podataka WorldWideImporters. Možemo pogledati sadržaj te stranice pomoću nedokumentiranog DBCC PAGE i trace flag 3604.
Napomena: Preferiram koristiti DBCC PAGE na obnovljenoj verziji iz sigurnosne kopije negdje na drugom serveru, jer je to nedokumentirana funkcija. U nekim slučajevima, ona može dovesti do stvaranja dumpa (napomena prevoditelja — nažalost, link ne vodi nigdje, ali sudeći prema URL-u, govori se o filtriranim indeksima).

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

Pomicanjem do rezultata, možete pronaći object_id i index_id.

Gotovo je! Sada možete pronaći imena sheme i indeksa pomoću upita:

USE WideWorldImporters;
GO
SELECT 
    sc.name as shema_ime, 
    so.name as objekt_ime, 
    si.name as indeks_ime
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 evo vidimo da je čekanje na zaključavanje bilo na indeksu PK_Sales_OrderLines tablice Sales.OrderLines.

Napomena: u SQL Server 2014 i novijim verzijama ime objekta također se može pronaći pomoću nedokumentirane DMO sys.dm_db_database_page_allocations. No, morat ćete provjeriti svaku stranicu u BDP-u, što ne izgleda baš dobro za velike baze podataka, stoga sam koristila DBCC PAGE.

1.4) Možemo li vidjeti podatke na toj stranici koja je bila zaključana?

Pa, da. Ali… jeste li sigurni da vam to stvarno treba?
To je sporo čak i na malim tablicama. No, to je zanimljivo, pa s obzirom da ste do sada čitali… razgovarajmo o %%physloc%%!

%%physloc%% je nedokumentirani komad magije koji vraća fizički identifikator za svaki zapis. Možete koristiti %%physloc%% zajedno sa sys.fn_PhysLocFormatter u SQL Serveru 2008 i novijim verzijama.

Sada kada znamo da želimo postaviti zaključavanje na stranicu u Sales.OrderLines, možemo pogledati sve podatke u ovoj tablici koji se nalaze u datotečnom fajlu #3 na stranici #70133, pomoću ovakvog upita:

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

Kao što sam rekla — to je sporo čak i na vrlo malim tablicama. Dodala sam NOLOCK u upit jer ionako nemamo nikakve garancije da su podaci koje želimo pogledati doista oni koji su bili u trenutku kada je zaključavanje otkriveno — stoga slobodno možemo napraviti ‘prljava čitanja’.
Ali, uživajte, upit mi vraća tih 25 redaka za koje se naš upit borio

Dosta o PAGE zaključavanjima. Što ako čekamo KEY zaključavanje?

2) waitresource=“KEY: 6:72057594041991168 (ce52f92a058c)” = Database_Id, HOBT_Id (čarobni hash koji se može dešifrirati pomoću %%lockres%%, ako to stvarno želite)

Ako vaš upit pokušava postaviti zaključavanje na zapis u indeksu i sam se nađe zaključan, dobijate sasvim drugačiji tip adrese.
Razbijajući “6:72057594041991168 (ce52f92a058c)” na dijelove, dobijamo:

database_id = 6
hobt_id = 72057594041991168
čarobni hash = (ce52f92a058c)

2.1) Dešifriramo database_id

Ovo funkcionira na isti način kao i u gornjem primjeru! Pronađite ime DB pomoću upita:

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

U mom slučaju — to je sve isto Baza podataka WideWorldImporters.

2.2) Dešifriramo hobt_id

U kontekstu pronađene DB, potrebno je izvršiti upit prema sys.partitions s parom spajanja koji će pomoći u određivanju imena tablice i indeksa...

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

Govori mi da je upit čekao na zaključavanju Application.Countries, koristeći indeks PK_Application_Countries.

2.3) Sada malo magije %%lockres%% — ako želite saznati koja je stavka bila zaključana

Ako stvarno želim znati na kojoj je redku bila potrebna zaključavanje, mogu to saznati upitom prema samoj tablici. Možemo koristiti nedokumentiranu funkciju %%lockres%% da pronađemo stavku koja odgovara čarobnom heksu.
Imajte na umu da će ovaj upit skenirati cijelu tablicu, a na velikim tablicama to može biti vrlo neugodno:

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

Dodala sam NOLOCK (po savjetu Klausa Aschenbrennera na Twitteru) jer zaključavanja mogu postati problem. Želimo samo pogledati što je trenutno tamo, a ne što je bilo kada je transakcija započela — ne mislim da nam je dosljednost podataka važna.
Evo ga, zapis za koji smo se borili!

Zahvale i daljnje čitanje

Ne sjećam se tko je prvi opisao mnoge od ovih stvari, ali evo dva posta o najmanje dokumentiranim stvarima koje bi vam se mogle svidjeti:

Post Paul Randal o %%physloc%% i sys.fn_PhysLocFormatter (kako smo pronašli naše podatke u prvom primjeru)
Pitanje na StackOverflow o korištenju %%lockres%% (kako smo pronašli podatke u drugom primjeru). Jedan od odgovora vodi do posta Granta Fritcheya o %%lockres%%, objavljenog još 2010..

Izvor: habr.com