Ak používate správu o zablokovanom procese alebo pravidelne zbierate grafy zablokovania poskytované serverom SQL Server, stretnete sa s týmito vecami:
waitresource="STRÁNKA: 6:3:70133"
waitresource=“KEY: 6:72057594041991168 (ce52f92a058c)“
Niekedy bude v tomto obrovskom XML, ktorý študujete, viac informácií (grafy uviaznutia obsahujú zoznam zdrojov, ktoré vám pomôžu zistiť názvy objektu a indexu), ale nie vždy.
Tento text vám ich pomôže rozlúštiť.
Všetky informácie, ktoré sú tu, sú na internete na rôznych miestach, sú jednoducho veľmi distribuované! Chcem to dať dokopy, od DBCC PAGE cez hobt_id až po nezdokumentované funkcie %%physloc%% a %%lockres%%.
Najprv si povedzme o čakaní na zámky PAGE a potom prejdime na zámky KEY.
1) waitresource="PAGE: 6:3:70133" = Database_Id: FileId: PageNumber
Ak vaša požiadavka čaká na uzamknutie PAGE, SQL Server vám poskytne adresu tejto stránky.
Rozdelením "PAGE: 6:3:70133" dostaneme:
- Database_id = 6
- data_file_id = 3
- číslo_strany = 70133
1.1) Dešifrujte database_id
Nájdite názov databázy pomocou dotazu:
SELECT
name
FROM sys.databases
WHERE database_id=6;
GOJe to verejné na mojom SQL Serveri.
1.2) Hľadajte názov dátového súboru - ak máte záujem
V ďalšom kroku použijeme data_file_id na nájdenie názvu tabuľky. Môžete jednoducho prejsť na ďalší krok, ale ak vás zaujíma názov súboru, môžete ho nájsť spustením dotazu v kontexte nájdenej databázy, pričom do tohto dotazu nahradíte data_file_id:
USE WideWorldImporters;
GO
SELECT
name,
physical_name
FROM sys.database_files
WHERE file_id = 3;
GOV databáze WideWorldImporters je to súbor s názvom WWI_UserData a mám ho obnovený do C:MSSQLDATAWideWorldImporters_UserData.ndf. (Ups, prichytili ste ma, ako vkladám súbory na systémový disk! Nie! Je to trápne).
1.3) Získajte názov objektu z DBCC PAGE
Teraz vieme, že stránka #70133 v dátovom súbore 3 patrí do databázy WorldWideImporters. Na obsah tejto stránky sa môžeme pozrieť pomocou nezdokumentovanej DBCC PAGE a príznaku sledovania 3604.
Poznámka: Dávam prednosť použitiu DBCC PAGE na obnovenej kópii niekde na inom serveri, pretože ide o nezdokumentované veci. V niektorých prípadoch aj ona (približne. prekladač - odkaz bohužiaľ nikam nevedie, ale súdiac podľa url, hovoríme o filtrovaných indexoch).
/* 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
Posúvaním k výsledkom môžete nájsť object_id a index_id.
Skoro hotové! Teraz môžete nájsť názvy tabuliek a indexov pomocou dotazu:
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;
GOA tu vidíme, že čakanie na zámok bolo na indexe PK_Sales_OrderLines tabuľky Sales.OrderLines.
Poznámka: V SQL Server 2014 a novších verziách možno názov objektu nájsť aj pomocou nezdokumentovaného DMO sys.dm_db_database_page_allocations. Musíte však vyhľadávať každú stránku v databáze, čo nevyzerá veľmi dobre pre veľké databázy, takže som použil DBCC PAGE.
1.4) Je možné vidieť údaje na stránke, ktorá bola zablokovaná?
Nuuu, áno. Ale... si si istý, že to naozaj potrebuješ?
Ide to pomaly aj na malých stoloch. Ale je to v pohode, takže keď ste sa dočítali až sem...hovorme o %%physloc%%!
%%physloc%% je nezdokumentované kúzlo, ktoré vracia fyzické ID pre každý záznam. môžeš použiť .
Teraz, keď vieme, že sme chceli zablokovať stránku v Sales.OrderLines, môžeme sa pozrieť na všetky údaje v tejto tabuľke, ktorá je uložená v dátovom súbore #3 na strane #70133, pomocou nasledujúceho dotazu:
Use WideWorldImporters;
GO
SELECT
sys.fn_PhysLocFormatter (%%physloc%%),
*
FROM Sales.OrderLines (NOLOCK)
WHERE sys.fn_PhysLocFormatter (%%physloc%%) like '(3:70133%'
GOAko som povedal, je to pomalé aj na maličkých stoloch. K žiadosti som pridal NOLOCK, pretože stále nemáme žiadne záruky, že údaje, na ktoré sa chceme pozrieť, sú úplne rovnaké ako v čase, keď bol zámok objavený - takže môžeme bezpečne robiť špinavé čítania.
Ale hurá, dotaz mi vráti presne 25 riadkov, o ktoré náš dotaz bojoval.
Dosť o PAGE zámkoch. Čo ak čakáme na zámok KĽÚČA?
2) waitresource="KEY: 6:72057594041991168 (ce52f92a058c)" = Database_Id, HOBT_Id (magický hash, ktorý možno dešifrovať pomocou %%lockres%%, ak naozaj chcete)
Ak sa váš dotaz pokúsi uzamknúť položku indexu a sám sa uzamkne, získate úplne iný typ adresy.
Rozdelením „6:72057594041991168 (ce52f92a058c)“ na časti dostaneme:
- Database_id = 6
- hobt_id = 72057594041991168
- magický hash = (ce52f92a058c)
2.1) Dešifrujte database_id
Funguje to úplne rovnako ako v príklade vyššie! Názov databázy nájdeme pomocou dotazu:
SELECT
name
FROM sys.databases
WHERE database_id=6;
GOV mojom prípade je to to isté .
2.2) Dešifrujte hobt_id
V súvislosti s nájdenou databázou sa musíte dotazovať na sys.partitions s niekoľkými spojeniami, ktoré vám pomôžu určiť názvy tabuľky a indexu ...
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;
GOHovorí mi, že požiadavka čakala na zámku Application.Countries pomocou indexu PK_Application_Countries.
2.3) Teraz trochu mágie %%lockres%% - ak chcete zistiť, ktorý záznam bol zamknutý
Ak naozaj chcem vedieť, na ktorom riadku bol zámok potrebný, môžem to zistiť dotazom na samotnú tabuľku. Môžeme použiť nezdokumentovanú funkciu %%lockres%% na nájdenie záznamu, ktorý sa zhoduje s magickým hashom.
Upozorňujeme, že tento dotaz prehľadá celú tabuľku a na veľkých tabuľkách to nemusí byť vôbec zábavné:
SELECT
*
FROM Application.Countries (NOLOCK)
WHERE %%lockres%% = '(ce52f92a058c)';
GO
Pridal som NOLOCK (), pretože zámky môžu byť problémom. Chceme sa len pozrieť na to, čo je tam teraz, a nie na to, čo tam bolo, keď sa transakcia začala – nemyslím si, že konzistencia údajov je pre nás dôležitá.
Voila, rekord, o ktorý sme bojovali!
Poďakovanie a ďalšie čítanie
Nepamätám si, kto prvý opísal mnohé z týchto vecí, ale tu sú dva príspevky o najmenej zdokumentovaných veciach, ktoré by sa vám mohli páčiť:
- Príspevok Paula Randala o (ako máme naše údaje v prvom príklade)
- Otázka týkajúca sa StackOverflow (ako sme našli údaje v druhom príklade). Jedna z odpovedí vedie k príspevku .
Zdroj: hab.com