🥇Dekodeerime Key ja Page WaitResource ummikutes ja lukustustes

Kui kasutate blokeeritud protsesside aruannet (blocked process report) või kogute SQL Serveri poolt pakutavaid deadlock graafe, kohtate te perioodiliselt järgmisi asju:

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

Mõnikord on selles hiiglaslikus XML-is, mida te uurite, rohkem teavet (deadlock graafides on ressursid, mis aitavad objekte ja indekseid tuvastada), kuid mitte alati.

See tekst aitab teil neid dešifreerida.

Kogu teave, mis siin on, on internetis erinevates kohtades olemas, see on lihtsalt väga laiali jaotatud! Ma tahan selle kõik kokku koguda — alates DBCC PAGE-st kuni hobt_id ja dokumenteerimata %%physloc%% ja %%lockres%% funktsioonideni.

Esiteks räägime PAGE-blokeeringute ootamisest ja seejärel liigume KEY-blokeeringute juurde.

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

Kui teie päring ootab PAGE-blokeeringut, annab SQL Server teile selle lehe aadressi.

Lahutades "PAGE: 6:3:70133", saame:

database_id = 6
data_file_id = 3
page_number = 70133

1.1) Dešifreerime database_id

Leidke andmebaasi nimi, kasutades päringut:

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

See on avalik andmebaas WideWorldImporters minu SQL Serveris.

1.2) Otsime andmefaili nime — kui see huvitab teid

Kavatseme kasutame data_file_id järgmises sammus, et leida tabeli nimi. Võite lihtsalt edasi liikuda, kuid kui teid huvitab faili nimi, leiate selle, tehes päringu leitud andmebaasis, asendades query_sellen data_file_id-ga:

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

Andmebaasis WideWorldImporters on see fail nimega WWI_UserData ja see on taastatud minu jaoks C:MSSQLDATAWideWorldImporters_UserData.ndf. (Ups, sa tabasid mind just sel hetkel, kui ma faile kõvakettale panin! Ei! Jube piinlik.)

1.3) Saame objekti nime DBCC PAGE-st

Nüüd teame, et lehekülg #70133 andmefailis 3 kuulub andmebaasile WorldWideImporters. Saame selle lehe sisu vaadata dokumenteerimata DBCC PAGE abil ja jälgimislipiku 3604 abil.
Märkus: eelistan kasutada DBCC PAGE-d taastatud varukoopia koopia peal, mis asub kuskil teisel serveril, kuna see on dokumenteerimata asi. Mõnes olukorras võib see võib põhjustada mälestuse loomise (tõlkija märkuseks — link viib kahjuks ei kuhugi, kuid URL-i järgi näib teema olevat filtreeritud indeksite kohta).

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

Kui tulemusi sirvida, võib leida object_id ja index_id.

Peaaegu valmis! Nüüd saab tabelite ja indeksite nimesid leida päringuga:

USE WideWorldImporters;
GO
SELECT 
    sc.name as schema_nimi, 
    so.name as objekti_nimi, 
    si.name as indeks_nimi
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

Ja siin me näeme, et ootamine lukustamisel oli indeksi PK_Sales_OrderLines tabelis Sales.OrderLines.

Märkus: SQL Server 2014 ja uuemates versioonides saab objekti nime leida ka dokumenteerimata DMO sys.dm_db_database_page_allocations abil. Kuid peate küsima iga lehe DB-s, mis ei tundu suurte andmebaaside jaoks eriti ägedana, seega kasutasin DBCC PAGE.

1.4) Kas on võimalik näha andmeid lukustatud lehe kohta?

Noh, jah. Aga... kas olete kindel, et see on tõesti vajalik?
See on aeglane isegi väikeste tabelite puhul. Kuid see on kuidagi äge, nii et kuna olete selle punktini jõudnud... rääkigem %%physloc%%-st!

%%physloc%% on dokumenteerimata maagia tükk, mis tagastab iga kirje füüsilise identifikaatori. Saate kasutada %%physloc%% koos sys.fn_PhysLocFormatteriga SQL Serveris 2008 ja uuemates..

Nüüd, kui me teame, et soovisime lehe Sales.OrderLines blokeerimist, saame vaadata kõiki andmeid selles tabelis, mis on salvestatud andmefailis #3 lehe #70133 peal, järgmise päringu abil:

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

Nagu ma ütlesin — see on isegi väikestes tabelites aeglane. Lisasin päringule NOLOCK, sest meil ei ole ikkagi mingeid garantiisid, et andmed, mida tahame vaadata, on need samad, mis olid hetkel, kui blokeering avastati — seega võime rahulikult teha räpaseid lugemisi.
Aga, hurraa, päring tagastab mulle need 25 rida, mille nimel meie päring võitles

Piisavalt PAGE-blokeeringutest. Mis juhtub, kui ootame KEY-blokeeringut?

2) waitresource=“KEY: 6:72057594041991168 (ce52f92a058c)” = Database_Id, HOBT_Id (salajane hash, mille saab dekrüpteerida %%lockres%% abil, kui tõeliselt soovite)

Kui teie päring püüab blokeerida salvestust indeksis ja jääb ise blokeerituks, saate hoopis teistsuguse aadressi.
Lahutades “6:72057594041991168 (ce52f92a058c)” osadeks, saame:

database_id = 6
hobt_id = 72057594041991168
salajane hash = (ce52f92a058c)

2.1) Dekodeerime database_id

See töötab täpselt nii nagu eespool oleva näite puhul! Otsime andmebaasi nime päringu kaudu:

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

Minu puhul on see ikka sama andmebaas WideWorldImporters.

2.2) Dekodeerime hobt_id

Leitud andmebaasi kontekstis tuleb teha päring sys.partitions tabelisse koos paariga joinidest, mis aitavad tuvastada tabeli ja indeksi nimed...

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

See ütleb mulle, et päring ootas blokkeerimist Application.Countries, kasutades indeksi PK_Application_Countries.

2.3) Nüüd pisut võlu %%lockres%% — kui soovite välja selgitada, milline kirje oli lukustatud

Kui ma tõeliselt tahan teada, millisel real lukustus oli vajalik, saan selle välja selgitada päringuga tabeli enda peale. Saame kasutada dokumenteerimata funktsiooni %%lockres%%, et leida kirje, mis vastab maagilisele räsi.
Pidage meeles, et see päring skannib kogu tabelit, ja suurte tabelite korral võib see olla üsna tülikas:

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

Olen lisanud NOLOCK (Klaus Aschenbrenneri soovitusel Twitteris) kuna blokeeringud võivad probleemiks muutuda. Me tahame lihtsalt vaadata, mis seal hetkel toimub, mitte seda, mis seal oli, kui tehing algas — ei arva, et andmete järjepidevus on meile oluline.
Võta näpust, see on rekord, mille nimel me võitlesime!

Tänud ja edasine lugemine

Ei mäleta, kes esimesena paljusid neist asjadest kirjutas, aga siin on kaks postitust kõige vähem dokumenteeritud asjadest, mis võivad teile meeldida: