Haddii aad isticmaasho warbixinta nidaamka la xannibay ama aad ururiso garaafyada xannibaadda ee ay bixiso SQL Server si xilliyo ah, waxaad la kulmi doontaa sidan oo kale:
waitresource="BOG: 6:3:70133"
waitresource=βKEY: 6:72057594041991168 (ce52f92a058c)β
Mararka qaarkood, waxaa jiri doona macluumaad dheeraad ah oo ku jira XML weyn oo aad barato (garaafyada xannibaadda waxay ka kooban yihiin liis agab ah oo kaa caawinaya inaad ogaato magacyada shayga iyo tusmooyinka), laakiin had iyo jeer maaha.
Qoraalkan ayaa kaa caawin doona inaad qeexdo.
Dhammaan macluumaadka halkan ku yaal waxay ku yaalliin internetka meelo kala duwan, si fudud ayaa loo qaybiyaa! Waxaan rabaa in aan isku wada geeyo, laga bilaabo DBCC PAGE ilaa hobt_id ilaa %% physloc%% aan sharciyeysneyn iyo %%lockres%% shaqooyinka.
Marka hore, aan ka hadalno sugidda qufulka PAGE, ka dibna u gudubno furaha qufullada.
1) waitresource="PAGE: 6:3:70133" = Database_Id: FileId: PageNumber
Haddii codsigaagu uu ku sugayo quful PAGE ah, SQL Server ayaa ku siin doona ciwaanka boggaas.
Jebinta "BOGGA: 6:3:70133" waxaan heleynaa:
- database_id = 6
- data_file_id = 3
- bogga_lambar = 70133
1.1) Decrypt database_id
Soo hel magaca kaydka xogta adoo isticmaalaya waydiinta:
SELECT
name
FROM sys.databases
WHERE database_id=6;
GOWaa dadwayne on my SQL Server.
1.2) Raadinta magaca faylka xogta - haddii aad xiisaynayso
Waxaan u adeegsan doonnaa data_file_id tallaabada xigta si aan u helno magaca miiska. Waxaad kaliya u boodi kartaa tallaabada xigta, laakiin haddii aad xiisaynayso magaca faylka, waxaad ka heli kartaa adiga oo ku socodsiinaya su'aal ku jirta xogta xogta la helay, adigoo ku beddelaya data_file_id weydiintan:
USE WideWorldImporters;
GO
SELECT
name,
physical_name
FROM sys.database_files
WHERE file_id = 3;
GOXogta WideWorldImporters kani waa fayl la yiraahdo WWI_UserData oo aan dib ugu celiyay C:MSSQLDATAWideWorldImporters_UserData.ndf. (Hagaag, waxaad igu qabatay anigoo galalka saaraya nidaamka wadista! Maya! Waa wax laga xishoodo).
1.3) Ka hel magaca shayga DBCC PAGE
Waxaan hadda ognahay in bogga #70133 ee ku jira datafile 3 uu iska leeyahay xog ururinta WorldWideImporters. Waxaan ku eegi karnaa waxa ku jira boggan anagoo adeegsanayna bogga DBCC ee aan sharciyeysnayn iyo calanka 3604.
Fiiro gaar ah: Waxaan doorbidayaa inaan ku isticmaalo DBCC PAGE nuqul dib loo soo celiyay meel server kale ah, sababtoo ah waa waxyaabo aan sharciyeysnayn. Xaaladaha qaarkood, iyada (qiyaastii turjumaan - isku xirka, nasiib daro, meelna ma horseedo, laakiin adoo xukumaya url, waxaan ka hadlaynaa tusmooyinka la sifeeyay).
/* 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
Markaad u guurto natiijooyinka, waxaad heli kartaa object_id iyo index_id.
Ku dhawaad ββdhammaatay! Hadda waxaad ka heli kartaa shaxda iyo magacyada tusmooyinka leh su'aal:
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;
GOOo halkan waxaan ku aragnaa in sugitaanka qufulku uu ku yaal PK_Sales_OrderLines index of Sales.OrderLines.
Xusuusin: Gudaha SQL Server 2014 iyo wixii ka sareeya, magaca shayga waxa kale oo laga heli karaa iyadoo la isticmaalayo DMO sys.dm_db_database_page_allocations aan sharciyeysnayn. Laakiin waa inaad waydiisaa bog kasta oo ku jira kaydka xogta, kaas oo aan aad ugu muuqan kaydka xogta waaweyn, markaa waxaan isticmaalay DBCC PAGE.
1.4) Suurtagal ma tahay in la arko xogta bogga la xannibay?
Nuuu, haa. Laakiin...ma hubtaa inaad runtii u baahan tahay?
Way gaabis tahay xataa miisaska yaryar. Laakin waa wax fiican, markaa maadaama aad akhriday ilaa hadda... aan ka hadalno %% physloc%%!
%% physloc%% waa qayb sixir ah oo aan sharciyeysnayn oo soo celisa aqoonsi jireed gelitaan kasta. waad isticmaali kartaa .
Hadda oo aan ognahay inaan rabnay inaan xannibno bogga Sales.OrderLines, waxaan eegi karnaa dhammaan xogta ku jirta shaxdan, taas oo ku kaydsan faylka xogta #3 ee bogga #70133, oo leh su'aasha soo socota:
Use WideWorldImporters;
GO
SELECT
sys.fn_PhysLocFormatter (%%physloc%%),
*
FROM Sales.OrderLines (NOLOCK)
WHERE sys.fn_PhysLocFormatter (%%physloc%%) like '(3:70133%'
GOSida aan idhi, way gaabis tahay xataa miisaska yaryar. Waxaan ku daray NOLOCK codsiga sababtoo ah weli ma haysanno dammaanad qaad ah in xogta aan rabno inaan eegno inay la mid tahay sidii ay ahayd markii la helay qufulka - si aan si badbaado leh u samayn karno akhrin wasakh ah.
Laakin, hooray, waydiintu waxay ii soo celisay 25 saf oo su'aashayadu u dagaalantay.
Ku filan qufullada PAGE Ka warran haddii aan sugayno quful FURAHA ah?
2) waitresource="KEY: 6:72057594041991168(ce52f92a058c)" = Database_Id, HOBT_Id
Haddii su'aashaadu ay isku daydo inay ku xidho gelinta tusaha oo ay iskeed u xidho, waxaad helaysaa nooc ka duwan ciwaanka.
U jebinta "6:72057594041991168 (ce52f92a058c)" qaybo, waxaanu helnaa:
- database_id = 6
- hobt_id = 72057594041991168
- hash sixir = (ce52f92a058c)
2.1) Decrypt database_id
Waxay u shaqeysaa si la mid ah tusaalaha kore! Waxaan ku helnaa magaca xogta anagoo adeegsanayna weydiinta:
SELECT
name
FROM sys.databases
WHERE database_id=6;
GOXaaladeyda, waa isku mid .
2.2) Decrypt hobt_id
Macnaha guud ee xogta la helay, waxaad u baahan tahay inaad waydiiso sys.partitions oo wata dhawr isku biir oo kaa caawin doona go'aaminta magacyada miiska iyo tusmooyinka.
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;
GOWaxay ii sheegtaa in codsigu ku sugayey Codsiga. Waddamada quful iyaga oo isticmaalaya tusmada PK_Application_Countries.
2.3) Hadda qaar %% lockres%% sixir - haddii aad rabto inaad ogaato gelitaanka la xiray
Haddii aan runtii rabo inaan ogaado safka loo baahan yahay qufulka, waxaan ku ogaan karaa anigoo waydiinaya miiska laftiisa. Waxaan u isticmaali karnaa shaqada %%lockres%% aan sharciyeysneyn si aan u helno gelitaan u dhigma xashiishka sixirka.
Ogsoonow in su'aashani ay sawiri doonto miiska oo dhan, iyo miisaska waaweyn waxaa laga yaabaa in tani aysan xiiso u lahayn gabi ahaanba:
SELECT
*
FROM Application.Countries (NOLOCK)
WHERE %%lockres%% = '(ce52f92a058c)';
GO
Waxaan ku daray NOLOCK () sababtoo ah qufulku waxay noqon karaan dhibaato. Waxaan rabnaa oo kaliya inaan eegno waxa hadda jira, oo aan ahayn waxa jiray markii macaamilku bilaabmay - uma maleynayo in joogtaynta xogta ay muhiim noo tahay.
Voila, rikoorkii aan u dagaalannay!
Mahadnaq iyo akhrin dheeraad ah
Ma xasuusto cidda marka hore ku sifaysay wax badan oo ka mid ah waxyaalahan, laakiin halkan waxaa ah laba qoraal oo ku saabsan waxyaalaha ugu yar ee dukumeentiga ah ee laga yaabo inaad jeceshahay:
- Boostada Paul Randal ee ku saabsan (sida aan xogtayada ku hayno tusaalaha koowaad)
- Su'aal ku saabsan StackOverflow (sida aan xogta ku helnay tusaalaha labaad). Mid ka mid ah jawaabaha ayaa keenaya boostada .
Source: www.habr.com