🥇Decrypting Kiyi uye Peji WaitResource mune zvakafa nekukiya

Kana iwe ukashandisa iyo yakavharika process report kana kuunganidza iyo deadlock graphs inopiwa neSQL Server nguva nenguva, iwe uchasangana nezvinhu zvakaita seizvi:

waitresource="PEJI: 6:3:70133"

waitresource=“KEY: 6:72057594041991168 (ce52f92a058c)“

Dzimwe nguva, pachave neruzivo rwakawanda mune iyo hofori XML yaunodzidza (deadlock graphs ine runyorwa rwezviwanikwa zvinokubatsira kuwana mazita echinhu uye index), asi kwete nguva dzose.

Rugwaro urwu ruchakubatsira kuanzwisisa.

Ruzivo rwese rwuri pano rwuri paInternet munzvimbo dzakasiyana siyana, rwakangoparadzirwa zvakanyanya! Ndinoda kuzviisa pamwe chete, kubva kuDBCC PEJI kusvika hobt_id kune isina kunyorwa %% physloc%% uye %%lockres%% mabasa.

Chekutanga, ngatitaure nezvekumirira paPEJI kiyi, tobva taenda kune KEY kiyi.

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

Kana chikumbiro chako chakamirira paPEJI kukiya, SQL Server inokupa kero yeiyo peji.

Kuparura "PEJI: 6:3:70133" tinowana:

database_id = 6
data_file_id = 3
peji_nhamba = 70133

1.1) Decrypt database_id

Tsvaga zita re database uchishandisa mubvunzo:

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

Zviri pachena DB WideWorldImporters pane yangu SQL Server.

1.2) Kutsvaga zita re data faira - kana uchifarira

Isu tichashandisa data_file_id munhanho inotevera kutsvaga zita retafura. Iwe unogona kungosvetukira kune chinhanho chinotevera, asi kana iwe uchifarira zita refaira, unogona kuriwana nekumhanyisa mubvunzo mumamiriro eiyo yakawanikwa dhatabhesi, uchitsiva data_file_id mumubvunzo uyu:

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

Mudura reWideWorldImporters iri ifaira rinonzi WWI_UserData uye ndine radzoreredzwa kuC:MSSQLDATAWideWorldImporters_UserData.ndf. (Oops, wandibata ndichiisa mafaera pasystem drive! Aiwa! Zvinonyadzisa).

1.3) Tora zita rechinhu kubva kuDBCC PEJI

Isu tave kuziva kuti peji #70133 mune datafile 3 ndeyeWorldWideImporters dhatabhesi. Tinogona kutarisa zviri papeji ino tichishandisa isina kunyorwa DBCC PEJI uye kutsvaga mureza 3604.
Ongorora: Ini ndinosarudza kushandisa DBCC PEJI pane yakadzoserwa kopi pane imwe sevha, nekuti haina kunyorwa zvinhu. Mune zvimwe zviitiko, iye zvinogona kukonzera kuraswa (approx. muturikiri - chinongedzo, zvinosuruvarisa, hapana kwainotungamira, asi tichitonga neurl, tiri kutaura nezve yakasefa indexes.).

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

Nekupuruzira kune zvawanikwa, unogona kuwana chinhu_id uye index_id.

Ndakutopedza! Iye zvino unogona kuwana tafura uye mazita eindex nemubvunzo:

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

Uye pano tinoona kuti kumirira pakiyi kwaiva paPK_Sales_OrderLines index yeSales.OrderLines table.

Cherechedza: MuSQL Server 2014 nepamusoro, zita rechinhu rinogona kuwanikwa zvakare uchishandisa isina kunyorwa DMO sys.dm_db_database_page_allocations. Asi iwe unofanirwa kubvunza peji yega yega mudhatabhesi, iyo isingataridzike inotonhorera kune makuru dhatabhesi, saka ndakashandisa DBCC PEJI.

1.4) Zvinogoneka here kuona iyo data pane peji yakavharwa?

Nuuu, hongu. Asi… une chokwadi here kuti unoida chaizvo?
Inononoka kunyange pamatafura maduku. Asi zvinotonhorera, saka kubva iwe waverenga kusvika zvino ... ngatitaure nezve %%physloc%%!

%% physloc%% chikamu chemashiripiti chisina kunyorwa chinodzosera ID yemuviri pane yega yega. unogona kushandisa %% physloc%% pamwe chete ne sys.fn_PhysLocFormatter muSQL Server 2008 uye pamusoro.

Zvatave kuziva kuti taida kuvharisa peji muSales.OrderLines, tinokwanisa kutarisa data rese riri patafura iyi, inochengetwa mu data file #3 papeji #70133, nemubvunzo unotevera:

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

Sezvandataura, inononoka kunyangwe pamatafura madiki. Ndakawedzera NOLOCK kuchikumbiro nekuti isu hatisati tisina vimbiso yekuti data ratinoda kutarisa rakafanana neraive panguva iyo kukiya kwakawanikwa - kuti tigone kuverenga zvakasviba zvakachengeteka.
Asi, hooray, mubvunzo unondidzosera iwo mitsara makumi maviri neshanu iyo mubvunzo wedu wakarwirwa.

Zvakwana nezve PAGE makiyi. Ko kana takamirira KEY lock?

2) waitresource = "KEY: 6:72057594041991168 (ce52f92a058c)" = Database_Id, HOBT_Id (magic hashi inogona kudhiribheti ne %%lockres%% kana uchinyatsoda)

Kana muvhunzo wako ukayedza kukiya pane indekisi yekupinda uye wovharwa wega, unowana mhando yekero yakasiyana zvachose.
Kutyora "6: 72057594041991168 (ce52f92a058c)" muzvikamu, tinowana:

database_id = 6
hobt_id = 72057594041991168
magic hashi = (ce52f92a058c)

2.1) Decrypt database_id

Inoshanda zvakafanana nemuenzaniso uri pamusoro! Isu tinowana zita re database tichishandisa mubvunzo:

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

Munyaya yangu, zvakafanana DB WideWorldImporters.

2.2) Decrypt hobt_id

Mune mamiriro eiyo dhatabhesi yakawanikwa, unofanirwa kubvunza sys.partitions ine akati wandei majoini anozobatsira kuona mazita etafura uye index ...

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

Inondiudza kuti chikumbiro changa chakamirira paApplication.Nyika dzinokiya uchishandisa PK_Application_Countries index.

2.3) Ikozvino kune mamwe %%lockres%% mashiripiti - kana iwe uchida kuziva kuti ndeipi yekupinda yakavharwa.

Kana ndichinyatsoda kuziva kuti ndeipi mutsara waidiwa kukiya, ndinogona kuziva nekubvunza tafura pachayo. Tinogona kushandisa isina kunyorwa %%lockres%% basa kuti tiwane yekupinda inoenderana nemashiripiti hashi.
Ziva kuti mubvunzo uyu uchaongorora tafura yese, uye pamatafura makuru izvi zvinogona kunge zvisinganakidze zvachose:

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

Ndawedzera NOLOCK (pane zano raKlaus Aschenbrenner pa twitter) nekuti makiyi anogona kunetsa. Isu tinongoda kutarisa izvo zviripo izvozvi, uye kwete izvo zvaivepo pakatanga kutengeserana - handifunge kuti kuenderana kwedata kwakakosha kwatiri.
Voila, chinyorwa chatakarwira!

Kutenda uye kuwedzera kuverenga

Handiyeuke kuti ndiani akatanga kutsanangura zvakawanda zvezvinhu izvi, asi heano maposita maviri nezvezvinhu zvidiki zvakanyorwa zvaungade:

Paul Randal's post nezve %%physloc%% uye sys.fn_PhysLocFormatter (sezvatine data redu mumuenzaniso wekutanga)
Mubvunzo paStackOverflow nezve kushandisa %% zvivharo%% (sezvatakawana data mumuenzaniso wechipiri). Imwe yemhinduro inotungamira kune iyo post Grant Fritchey nezve %%lockres%% yakanyorwa kumashure muna 2010.

Source: www.habr.com

Decipher Kiyi uye Peji WaitResource mune zvakafa uye makiyi