Ako koristite izvješće o blokiranom procesu ili povremeno prikupljate grafikone zastoja koje pruža SQL Server, naići ćete na stvari poput ovoga:
waitresource="PAGE: 6:3:70133"
waitresource=“KEY: 6:72057594041991168 (ce52f92a058c)“
Ponekad će biti više informacija u tom divovskom XML-u koji proučavate (grafovi zastoja sadrže popis resursa koji vam pomaže da saznate imena objekata i indeksa), ali ne uvijek.
Ovaj tekst će vam pomoći da ih dešifrirate.
Sve informacije koje su ovdje nalaze se na internetu na raznim mjestima, jednostavno su jako raspoređene! Želim sve spojiti, od DBCC PAGE do hobt_id do nedokumentiranih funkcija %%physloc%% i %%lockres%%.
Prvo, razgovarajmo o čekanjima na PAGE zaključavanjima, a zatim prijeđimo na KEY zaključavanja.
1) waitresource="PAGE: 6:3:70133" = Database_Id: FileId: PageNumber
Ako vaš zahtjev čeka na zaključavanje stranice, SQL Server će vam dati adresu te stranice.
Rastavljanjem "PAGE: 6:3:70133" dobivamo:
- baza_id = 6
- ID_datoteke_podataka = 3
- broj_stranice = 70133
1.1) Dešifrirajte database_id
Pronađite naziv baze pomoću upita:
SELECT
name
FROM sys.databases
WHERE database_id=6;
GOTo je javno na mom SQL Serveru.
1.2) Tražite naziv podatkovne datoteke - ako ste zainteresirani
Koristit ćemo data_file_id u sljedećem koraku da pronađemo naziv tablice. Možete samo preskočiti na sljedeći korak, ali ako vas zanima naziv datoteke, možete ga pronaći pokretanjem upita u kontekstu pronađene baze podataka, zamjenom data_file_id u ovaj upit:
USE WideWorldImporters;
GO
SELECT
name,
physical_name
FROM sys.database_files
WHERE file_id = 3;
GOU bazi podataka WideWorldImporters ovo je datoteka pod nazivom WWI_UserData i ja sam je vratio u C:MSSQLDATAWideWorldImporters_UserData.ndf. (Ups, uhvatili ste me kako stavljam datoteke na sistemski pogon! Ne! To je neugodno).
1.3) Dobiti naziv objekta iz DBCC STRANICE
Sada znamo da stranica #70133 u podatkovnoj datoteci 3 pripada bazi podataka WorldWideImporters. Možemo pogledati sadržaj ove stranice koristeći nedokumentiranu DBCC STRANICU i oznaku praćenja 3604.
Napomena: radije koristim DBCC PAGE na obnovljenoj kopiji negdje na drugom poslužitelju, jer su to nedokumentirane stvari. U nekim slučajevima, ona (cca. prevoditelj - link, nažalost, ne vodi nikamo, ali sudeći po url-u, govorimo 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.
Skoro gotovo! Sada možete pronaći nazive tablica i indeksa pomoću upita:
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;
GOI ovdje vidimo da je čekanje na zaključavanje bilo na indeksu PK_Sales_OrderLines tablice Sales.OrderLines.
Napomena: U SQL Serveru 2014 i novijim, naziv objekta također se može pronaći pomoću nedokumentiranog DMO sys.dm_db_database_page_allocations. Ali morate postaviti upit za svaku stranicu u bazi podataka, što ne izgleda baš super za velike baze podataka, pa sam upotrijebio DBCC PAGE.
1.4) Je li moguće vidjeti podatke na stranici koja je blokirana?
Nuuu, da. Ali... jeste li sigurni da vam stvarno treba?
Spor je čak i na malim stolovima. Ali nekako je cool, pa budući da ste pročitali dovde...razgovarajmo o %%physloc%%!
%%physloc%% nedokumentirana je magija koja vraća fizički ID za svaki unos. možeš koristiti .
Sada kada znamo da smo htjeli blokirati stranicu u Sales.OrderLines, možemo pogledati sve podatke u ovoj tablici, koja je pohranjena u podatkovnoj datoteci #3 na stranici #70133, sa sljedećim upitom:
Use WideWorldImporters;
GO
SELECT
sys.fn_PhysLocFormatter (%%physloc%%),
*
FROM Sales.OrderLines (NOLOCK)
WHERE sys.fn_PhysLocFormatter (%%physloc%%) like '(3:70133%'
GOKao što sam rekao, sporo je čak i na malim stolovima. Dodao sam NOLOCK zahtjevu jer još uvijek nemamo jamstva da su podaci koje želimo pogledati potpuno isti kao što su bili u trenutku kada je zaključavanje otkriveno - tako da možemo sigurno raditi prljava čitanja.
Ali, hura, upit mi vraća upravo onih 25 redaka za koje se naš upit borio.
Dosta o zaključavanju STRANICE. Što ako čekamo bravu KLJUČ?
2) waitresource="KEY: 6:72057594041991168 (ce52f92a058c)" = Database_Id, HOBT_Id (magični hash koji se može dešifrirati s %%lockres%% ako to stvarno želite)
Ako vaš upit pokuša zaključati unos indeksa i sam se zaključa, dobit ćete potpuno drugu vrstu adrese.
Razbijanjem "6:72057594041991168 (ce52f92a058c)" u dijelove, dobivamo:
- baza_id = 6
- hobt_id = 72057594041991168
- čarobni hash = (ce52f92a058c)
2.1) Dešifrirajte database_id
Radi potpuno isto kao u gornjem primjeru! Naziv baze podataka nalazimo pomoću upita:
SELECT
name
FROM sys.databases
WHERE database_id=6;
GOU mom slučaju je isto .
2.2) Dešifrirajte hobt_id
U kontekstu pronađene baze podataka, trebate postaviti upit sys.partitions s nekoliko spojeva 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;
GOKaže mi da je zahtjev čekao na zaključavanju Application.Countries pomoću indeksa PK_Application_Countries.
2.3) Sada malo %%lockres%% magije - ako želite saznati koji je unos zaključan
Ako stvarno želim znati u kojem je redu bilo potrebno zaključavanje, to mogu saznati upitom u samu tablicu. Možemo koristiti nedokumentiranu funkciju %%lockres%% da pronađemo unos koji odgovara magičnom hash-u.
Imajte na umu da će ovaj upit skenirati cijelu tablicu, a na velikim tablicama ovo možda uopće neće biti zabavno:
SELECT
*
FROM Application.Countries (NOLOCK)
WHERE %%lockres%% = '(ce52f92a058c)';
GO
Dodao sam NOLOCK () jer brave mogu biti problem. Samo želimo pogledati što je tamo sada, a ne što je bilo kada je transakcija započela - ne mislim da nam je dosljednost podataka važna.
Voila, rekord za koji smo se borili!
Zahvale i dodatna literatura
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 Paula Randala o (kao što imamo podatke u prvom primjeru)
- Pitanje na StackOverflowu o (kako smo pronašli podatke u drugom primjeru). Jedan od odgovora vodi do objave .
Izvor: www.habr.com