Ako koristite izvještaj o blokiranom procesu ili povremeno prikupljate grafove zastoja koje pruža SQL Server, naići ćete na sljedeće stvari:
waitresource=“PAGE: 6:3:70133“
waitresource=“KEY: 6:72057594041991168 (ce52f92a058c)“
Ponekad će biti više informacija u tom ogromnom XML-u koji proučavate (grafovi zastoja sadrže listu resursa koji vam pomažu da saznate nazive objekata i indeksa), ali ne uvijek.
Ovaj tekst će vam pomoći da ih dešifrujete.
Sve informacije koje se ovdje nalaze nalaze se na internetu na raznim mjestima, samo su veoma distribuirane! Želim sve spojiti - od DBCC PAGE do hobt_id i do nedokumentiranih funkcija %%physloc%% i %%lockres%%.
Prvo, hajde da pričamo o čekanju na zaključavanje PAGE, a zatim ćemo preći na KEY zaključavanje.
1) waitresource=“PAGE: 6:3:70133” = Database_Id: FileId: PageNumber
Ako vaš upit čeka na zaključavanje PAGE, SQL Server će vam dati adresu te stranice.
Rastavljajući “PAGE: 6:3:70133” dobijamo:
- id_baze podataka = 6
- data_file_id = 3
- broj_stranice = 70133
1.1) Dešifriranje baze_id
Pronađimo ime baze podataka pomoću upita:
SELECT
name
FROM sys.databases
WHERE database_id=6;
GOOvo je javno na mom SQL Serveru.
1.2) Tražite naziv datoteke sa podacima - ako ste zainteresovani
Koristit ćemo data_file_id u sljedećem koraku da pronađemo ime tabele. Možete jednostavno preskočiti na sljedeći korak, ali ako vas zanima ime datoteke, možete ga pronaći pokretanjem upita u kontekstu pronađene baze podataka, zamjenom data_file_id u ovom upitu:
USE WideWorldImporters;
GO
SELECT
name,
physical_name
FROM sys.database_files
WHERE file_id = 3;
GOU bazi podataka WideWorldImporters ovo je datoteka koja se zove WWI_UserData i vratila sam je u C:MSSQLDATAWideWorldImporters_UserData.ndf. (Ups, uhvatili ste me kako stavljam fajlove na sistemski disk! Ne! To je bilo nezgodno).
1.3) Dobijte ime objekta sa DBCC PAGE
Sada znamo da stranica #70133 u datoteci podataka 3 pripada bazi podataka WorldWideImporters. Možemo pogledati sadržaj ove stranice pomoću nedokumentirane DBCC PAGE i oznake praćenja 3604.
Napomena: Radije koristim DBCC PAGE na obnovljenoj kopiji iz sigurnosne kopije negdje na drugom serveru, jer je to nedokumentirana stvar. U nekim slučajevima ona (cca. prevodilac - link, nažalost, ne vodi nikuda, 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
Skrolujući kroz rezultate, 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 sada vidimo da je čekanje zaključavanja bilo na indeksu PK_Sales_OrderLines u tablici Sales.OrderLines.
Napomena: U SQL Serveru 2014 i novijim verzijama, naziv objekta se također može pronaći pomoću nedokumentiranog DMO-a sys.dm_db_database_page_allocations. Ali morate upiti svaku stranicu u bazi podataka, što ne izgleda baš cool za velike baze podataka, pa sam koristio DBCC PAGE.
1.4) Da li je moguće vidjeti podatke na stranici koja je blokirana?
Pa da. Ali... jeste li sigurni da vam je to zaista potrebno?
Sporo je čak i na malim stolovima. Ali to je nekako cool, pa pošto ste čitali do sada... hajde da pričamo o %%physloc%%!
%%physloc%% je nedokumentirani dio magije koji vraća fizički identifikator za svaki unos. možeš koristiti .
Sada kada znamo da želimo zaključati stranicu u Sales.OrderLines, možemo pogledati sve podatke u ovoj tabeli, koja je pohranjena u datoteci podataka #3 na stranici #70133, koristeći ovaj upit:
Use WideWorldImporters;
GO
SELECT
sys.fn_PhysLocFormatter (%%physloc%%),
*
FROM Sales.OrderLines (NOLOCK)
WHERE sys.fn_PhysLocFormatter (%%physloc%%) like '(3:70133%'
GOKao što sam rekao, spor je čak i na malim stolovima. Dodao sam NOLOCK zahtjevu jer još uvijek nemamo garanciju da su podaci koje želimo pogledati potpuno isti kao i kada je zaključavanje otkriveno - tako da možemo bezbedno vršiti prljava čitanja.
Ali, ura, upit mi vraća istih 25 redova za koje se borio naš upit
Dosta o zaključavanju PAGE. Šta ako čekamo ključnu bravu?
2) waitresource=“KEY: 6:72057594041991168 (ce52f92a058c)” = Database_Id, HOBT_Id (magični hash koji se može dešifrirati pomoću %%lockres%% ako to zaista želite)
Ako vaš upit pokuša zaključati zapis u indeksu i sam se zaključa, na kraju ćete dobiti potpuno drugačiji tip adrese.
Razbijajući “6:72057594041991168 (ce52f92a058c)” na dijelove, dobijamo:
- id_baze podataka = 6
- hobt_id = 72057594041991168
- magični hash = (ce52f92a058c)
2.1) Dešifriranje baze_id
Ovo radi potpuno isto kao i gornji primjer! Pronađite naziv baze podataka pomoću upita:
SELECT
name
FROM sys.databases
WHERE database_id=6;
GOU mom slučaju je i dalje isto .
2.2) Dešifriranje hobt_id
U kontekstu pronađene baze podataka, potrebno je izvršiti upit za sys.partitions s parom 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 koristeći indeks PK_Application_Countries.
2.3) Sada malo magije %%lockres%% - ako želite da saznate koji je unos zaključan
Ako zaista želim da znam u kom redu je zaključana, mogu saznati upitom u samoj tabeli. Možemo koristiti nedokumentiranu funkciju %%lockres%% da pronađemo unos koji odgovara magičnom hešu.
Imajte na umu da će ovaj upit skenirati cijelu tabelu, a na velikim stolovima ovo možda neće biti nimalo zabavno:
SELECT
*
FROM Application.Countries (NOLOCK)
WHERE %%lockres%% = '(ce52f92a058c)';
GO
Dodao sam NOLOCK () jer blokade mogu postati problem. Želimo samo da pogledamo šta je tu sada, a ne šta je bilo kada je transakcija počela - ne mislim da nam je konzistentnost podataka važna.
Voila, rekord za koji smo se borili!
Priznanja i dalje čitanje
Ne sjećam se ko 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 naše podatke u prvom primjeru)
- Pitanje o StackOverflowu o (kao što smo pronašli podatke u drugom primjeru). Jedan od odgovora vodi do posta .
izvor: www.habr.com