I-Decipher Key kunye ne-Page WaitResource kwizitshixo kunye nezitshixo

Ukuba usebenzisa ingxelo yenkqubo evaliweyo okanye uqokelele iigrafu zokuvala ezibonelelwa yi-SQL Server ngamaxesha athile, uya kudibana nezinto ezinje:

waitresource=“IPHEPHA: 6:3:70133“

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

Ngamanye amaxesha kuya kubakho ulwazi oluthe kratya kweso sigebenga se-XML ufundayo (iigrafu ezivaliweyo ziqulathe uluhlu lwemithombo ekuncedayo ukufumana into kunye namagama esalathiso), kodwa hayi rhoqo.

Lo mbhalo uza kukunceda uziqonde.

Lonke ulwazi olulapha lukwi-Intanethi kwiindawo ezahlukeneyo, lusasazwa kakhulu! Ndifuna ukuhlanganisa yonke into - ukusuka kwi-DBCC PAGE ukuya kwi-hobt_id kunye nakwi-%% physloc%% kunye ne-%% lockres%% engabhalwanga.

Kuqala, masithethe malunga nokulinda kwi-PAGE izitshixo, kwaye emva koko siya kudlulela kwi- KEY yokutshixa.

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

Ukuba umbuzo wakho ulinde kwi PAGE itshixiwe, iSQL Server iya kukunika idilesi yelo phepha.

Ukwahlulahlula "IPHEPHA: 6:3:70133" sifumana:

• i-database_id = 6
• idatha_file_id = 3
• page_numer = 70133

1.1) Decrypt database_id

Masifumane igama lesiseko sedatha sisebenzisa umbuzo:

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

Oku kuluntu DB WideWorldImporters kwiSeva yam yeSQL.

1.2) Ukukhangela igama lefayile yedatha - ukuba unomdla

Siza kusebenzisa idatha_file_id kwinyathelo elilandelayo ukufumana igama letafile. Ungatsiba ngokulula uye kwinyathelo elilandelayo, kodwa ukuba unomdla kwigama lefayile, ungayifumana ngokuqhuba umbuzo kumxholo wesiseko sedata esifunyenweyo, endaweni ye data_file_id kulo mbuzo:

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

Kwisiseko sedatha seWideWorldImporters le yifayile ebizwa ngokuba yiWWI_UserData kwaye ndiyibuyisele ku C:MSSQLDATAWideWorldImporters_UserData.ndf. (Owu, undibambe ndibeka iifayile kwidiski yesixokelelwano! Hayi! Bekunzima oko).

1.3) Fumana igama lento kwi-DBCC PAGE

Ngoku siyazi ukuba iphepha #70133 kwifayile yedatha 3 leleWorldWideImporters database. Sinokujonga imixholo yeli phepha sisebenzisa i-DBCC PAGE engabhalwanga kwaye silandele iflegi engu-3604.
Qaphela: Ndikhetha ukusebenzisa i-DBCC IPHEPHA kwikopi ebuyiselweyo ukusuka kwi-backup kwenye indawo kwenye iseva, kuba yinto engabhalwanga. Kwezinye iimeko, yena kunokukhokelela ekubeni kudalwe ukulahla (malunga. Umguquleli - ikhonkco, ngelishwa, akukho ndawo, kodwa ukugweba nge-url, sithetha malunga nezalathi ezihluziweyo.).

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

Ukuskrola kwiziphumo, ungafumana object_id kunye index_id.

Sele bugqiba! Ngoku ungafumana itheyibhile kunye namagama esalathisi usebenzisa umbuzo:

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

Kwaye ngoku siyabona ukuba ukutshixa ukulinda kwakukwi-PK_Sales_OrderLines index yeSales.OrderLines table.

Qaphela: Kwi-SQL Server ka-2014 kwaye kamva, igama lento lingafunyanwa kusetyenziswa i-DMO engabhalwanga sys.dm_db_database_page_allocations. Kodwa kuya kufuneka ubuze iphepha ngalinye kwidathabheyisi, engabonakali ipholile kakhulu kuluhlu lwedatha enkulu, ngoko ndisebenzise i-DBCC PAGE.

1.4) Ngaba kunokwenzeka ukubona idatha kwiphepha elivaliweyo?

Ewe, ewe. Kodwa ... uqinisekile ukuba uyayifuna ngokwenene?
Icotha nakwitafile ezincinci. Kodwa intle, ngoko ke ukuba ufunde ukuza kuthi ga ngoku... masithethe nge%%physloc%%!

I-%%physloc%% liqhekeza lomlingo elingabhalwanga elibuyisela isazisi esibonakalayo kwingeniso nganye. ungasebenzisa %% physloc%% kunye ne sys.fn_PhysLocFormatter kwi-SQL Server 2008 nangaphezulu.

Ngoku siyazi ukuba besifuna ukutshixa iphepha kwiSales.OrderLines, sinokujonga zonke iinkcukacha kule theyibhile, egcinwe kwifayile yedatha #3 kwiphepha #70133, sisebenzisa lo mbuzo:

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

Njengoko benditshilo, iyacotha nakwitafile ezincinci. Ndongeze u-NOLOCK kwesi sicelo kuba asikaqiniseki ukuba idatha esifuna ukuyijonga ifana nqwa naxa kwabhaqwa isitshixo- ukuze sifunde ngokukhuselekileyo.
Kodwa, khawuleza, umbuzo undibuyisela kwa imiqolo engama-25 efanayo umbuzo wethu owawulwela yona.

Kwanele malunga PAGE izitshixo. Kuthekani ukuba silindele isitshixo se KEY?

2) waitresource=“ISIHLOKO: 6:72057594041991168 (ce52f92a058c)” = I-Database_Id, HOBT_Id (i-hash yomlingo enokukhutshwa ngokuntsonkotha kusetyenziswa %%lockres%% ukuba uyayifuna ngenene loo nto)

Ukuba umbuzo wakho uzama ukutshixa irekhodi kwisalathiso kwaye uzitshixe ngokwalo, uphela nodidi olwahluke ngokupheleleyo lwedilesi.
Ukwaphula "6: 72057594041991168 (ce52f92a058c)" kwiindawo, sifumana:

• i-database_id = 6
• hobt_id = 72057594041991168
• ihashi yomlingo = (ce52f92a058c)

2.1) Decrypt database_id

Oku kusebenza ngokufanayo nomzekelo ongasentla! Fumana igama lesiseko sedatha usebenzisa umbuzo:

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

Kwimeko yam isafana DB WideWorldImporters.

2.2) Decrypt hobt_id

Kumxholo wesiseko sesiseko esifunyenweyo, kufuneka uphumeze umbuzo kwi-sys.izahlulo ngezibini zokudibanisa eziya kunceda ukumisela amagama etafile kunye nesalathisi...

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

Indixelela ukuba isicelo besilindile kwiSicelo.Amazwe atshixa esebenzisa i-PK_Application_Countries index.

2.3) Ngoku umlingo omncinci %%lockres%% - ukuba ufuna ukufumanisa ukuba loluphi ungeno olwalutshixiwe.

Ukuba ngenene ndifuna ukwazi ukuba ngowuphi umqolo isitshixo ebesikuwo, ndingafumanisa ngokubuza itafile ngokwayo. Singasebenzisa umsebenzi ongabhalwanga %%%lockres%% ukufumana ingeniso ehambelana nehashi yomlingo.
Nceda uqaphele ukuba lo mbuzo uyakuskena yonke itafile, kwaye kwiitafile ezinkulu oku akunakuba mnandi kwaphela:

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

Ndongeze NOLOCK (ngeengcebiso zikaKlaus Aschenbrenner kwi-Twitter) kuba imiqobo inokuba yingxaki. Sifuna nje ukujonga into ekhoyo ngoku, kwaye hayi into eyayikho xa kwaqala ukuthengiselana - andicingi ukuba ukuhambelana kwedatha kubalulekile kuthi.
Voila, irekhodi esililwelayo!

Imibulelo kunye nokufunda ngakumbi

Andikhumbuli ukuba ngubani oqale wachaza uninzi lwezi zinto, kodwa nazi izithuba ezibini malunga nezona zinto zibhalwe kancinci onokuthi uzithande:

• Isithuba sikaPaul Randal malunga %%physloc%% kunye sys.fn_PhysLocFormatter (njengoko sinayo idatha yethu kumzekelo wokuqala)
• Umbuzo ngeStackOverflow malunga usebenzisa %%izitshixo%% (njengoko sifumene idatha kumzekelo wesibini). Enye yeempendulo ikhokelela kwisithuba UGrant Fritchey malunga ne-%%lockres%%, eyabhalwa emva phaya ngo-2010.

umthombo: www.habr.com