🥇 Deshifrojmë Key dhe Page WaitResource në ndalimet dhe bllokimet

Nëse po përdorni raportin e bllokimeve (blocked process report) ose po mbledhni grafikonet e bllokimeve që ofron SQL Server, herë pas here do të përballeni me këto gjëra:

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

Ndonjëherë, në atë XML gjigant që po studioni, do të ketë më shumë informacion (grafikonet e bllokimeve përmbajnë një listë burimesh që ndihmojnë në zbërthimin e emrave të objekteve dhe indekseve), por jo gjithmonë.

Ky tekst do t'ju ndihmojë t'i zbërtheni.

Të gjitha informacionet që ndodhen këtu janë në internet në vende të ndryshme, thjesht janë shpërndarë shumë! Dua të mbledh gjithçka së bashku - nga DBCC PAGE deri te hobt_id dhe funksionet e pa dokumentuara %%physloc%% dhe %%lockres%%.

Së pari, do të flasim për pritjet në bllokimet PAGE, pastaj do të kalojmë te bllokimet KEY.

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

Nëse kërkesa juaj po pret në një bllokim PAGE, SQL Server do t'ju japë adresën e kësaj faqe.

Duke e thyer „PAGE: 6:3:70133“ ne marrim:

database_id = 6
data_file_id = 3
page_numer = 70133

1.1) Zbërthimi i database_id

Të gjejmë emrin e bazës së të dhënave duke përdorur kërkesën:

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

Kjo është e hapur për publikun Baza e të dhënave WideWorldImporters në SQL Server-in tim.

1.2) Po kërkojmë emrin e skedarit të të dhënave — nëse jeni të interesuar

Do të përdorim data_file_id në hapin e ardhshëm për të gjetur emrin e tabelës. Mund të kaloni në hapin tjetër, por nëse jeni të interesuar për emrin e skedarit, mund ta gjeni duke ekzekutuar një kërkesë në kontekstin e DB-së së gjetur, duke vendosur data_file_id në këtë kërkesë:

Përdor WideWorldImporters;
Shkoni
Zgjidhni 
    emri, 
    emrin_fizik
Prej sys.database_files
Këtu file_id = 3;
Shkoni

Në DB-në WideWorldImporters, ky skedar quhet WWI_UserData dhe është rikthyer te unë në C:MSSQLDATAWideWorldImporters_UserData.ndf. (Ups, më kapët si po vendosja skedarët në diskun e sistemit! Jo! Doli e çuditshme).

1.3) Marrim emrin e objektit nga DBCC PAGE

Tani e dimë se faqja #70133 në skedarin e të dhënave 3 i përket DB-së WorldWideImporters. Mund të shikojmë përmbajtjen e kësaj faqeje me DBCC PAGE të papërcaktuar dhe flamurin e gjurmës 3604.
Shënim: Preferoj të përdor DBCC PAGE në një kopje të rikthyer nga një backup diku në një server tjetër, sepse kjo është një gjë e papërcaktuar. Në disa raste, ajo mund të çojë në krijimin e një dumpi (shënimi i përkthyesit — lidhja, fatkeqësisht, çon askund, por sipas url-së, flitet për indeksat e filtruar).

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

Duke e arritur në rezultatet, mund të gjeni object_id dhe index_id.

Gati! Tani mund të gjeni emrat e tabelës dhe indeksit me anë të pyetjes:

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;
GO

Dhe ja, shohim se pritja në bllokim ishte në indeksin PK_Sales_OrderLines të tabelës Sales.OrderLines.

Shënim: Në SQL Server 2014 dhe më lart, emri i objektit gjithashtu mund të gjendet me anë të DMO-ës së dokumentuar sys.dm_db_database_page_allocations. Megjithatë, do t'ju duhet të kërkoni çdo faqe në BD, që duket paksa jo interesante për baza të mëdha të dhënash, kështu që përdora DBCC PAGE.

1.4) A mund të shohim të dhënat në atë faqe që ishte bllokuar?

Eh, po. Por... a jeni të sigurt se e keni vërtet nevojë për këtë?
Kjo është e ngadalshme edhe në tabelat e vogla. Por është disi interesante, kështu që, që e keni lexuar deri këtu... le të flasim për %%physloc%%!

%%physloc%% është një copë magjie e pa dokumentuar që kthen identifikuesin fizik për çdo rekord. Mund ta përdorni %%physloc%% së bashku me sys.fn_PhysLocFormatter në SQL Server 2008 dhe më lart..

Tani që dimë se dëshironim të bllokonim faqen në Sales.OrderLines, mund të shikojmë të gjitha të dhënat në këtë tabelë, të cilat ruhen në skedarin e të dhënave #3 në faqen #70133, duke përdorur këtë kërkesë:

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

Siç thashë — kjo është e ngadaltë edhe në tabela të vogla. E kam shtuar NOLOCK në kërkesë sepse sërish nuk kemi asnjë garanci që të dhënat që duam të shohim janë të njëjtat që ishin në momentin kur u zbulua bllokimi — kështu që mund ta bëjmë pa shqetësim lexim të papastër.
Por, urime, kërkesa më kthen ato 25 rreshta që kërkesa jonë po luftonte për t’i marrë.

Mjaft për bllokimet PAGE. Çfarë nëse pritemi për bllokimin KEY?

2) waitresource=“KEY: 6:72057594041991168 (ce52f92a058c)” = Database_Id, HOBT_Id (hash magjik, i cili mund të deshifrohet me %%lockres%%, nëse me të vërtetë dëshironit këtë)

Nëse kërkesa juaj përpiqet të vendosë një bllokim në një shënim në indeks dhe bllokohet vetë, merrni një lloj tërësisht të ndryshëm të adresës.
Duke e ndarë “6:72057594041991168 (ce52f92a058c)” në pjesë, ne marrim:

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

2.1) Zbulojme database_id

Kjo funksionon pikërisht si në shembullin e mësipërm! Gjejmë emrin e DB-së duke përdorur pyetjen:

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

Në rastin tim, kjo është e njëjta Baza e të dhënave WideWorldImporters.

2.2) Zbulojme hobt_id

Në kontekstin e DB-së së gjetur, duhet të ekzekutojmë një pyetje në sys.partitions me një çift bashkimesh, që do të ndihmojnë në përcaktimin e emrave të tabelës dhe indeksit…

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

Ai më thotë se pyetja priste në bllokimin Application.Countries, duke përdorur indeksin PK_Application_Countries.

2.3) Tani pak magji %%lockres%% — nëse dëshironi të zbulojmë se cila regjistër ishte bllokuar

Nëse vërtet dua të di se në cilën rresht duhej bllokimi, mund ta zbuloj këtë me një pyetje në vetë tabelën. Ne mund të përdorim funksionin e pa dokumentuar %%lockres%%, për të gjetur regjistrin që përputhet me hash-in magjik.
Mbani në mend se kjo pyetje do të skanojë tërë tabelën, dhe në tabela të mëdha, kjo mund të mos jetë aspak argëtuese:

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

Kam shtuar NOLOCK (sipas Klaus Aschenbrenner në Twitter) sepse bllokimet mund të jenë një problem. Ne duam vetëm të shikojmë se çfarë ka tani, e jo se çfarë ka pasur kur filloi transaksioni — nuk mendoj se konsistenca e të dhënave është e rëndësishme për ne.
Voa, regjistrimi për të cilin luftuam!

Faleminderit dhe lexim i mëtejshëm

Nuk e di kush e përshkroi i pari shumë nga këto gjëra, por ja dy postime për gjërat më pak të dokumentuara që mund t'ju pëlqejnë:

Postimi i Paul Randal për %%physloc%% dhe sys.fn_PhysLocFormatter (si për shembujt tanë në shembullin e parë)
Pyetja në StackOverflow për përdorimin e %%lockres%% (si e gjetëm të dhënat në shembullin e dytë). Një nga përgjigjet çon në postimin Grant Fritchey për %%lockres%%, shkruar që në vitin 2010.

Burimi: habr.com