🥇 Igodo decrypting na ibe WaitResource n'ime mkpọchi na igbochi

Ọ bụrụ na ị na-eji akụkọ usoro egbochiri ma ọ bụ na-anakọta eserese mkpọchi nke SQL Server na-enye kwa oge, ị ga-ezute ihe ndị dị ka nke a:

waitresource=“ PEEJI: 6:3:70133“

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

Mgbe ụfọdụ, a ga-enwe ozi ndị ọzọ na nnukwu XML ahụ ị na-amụ (eserese okwu mmechi nwere ndepụta akụrụngwa na-enyere gị aka ịchọpụta ihe na aha ndeksi), mana ọ bụghị mgbe niile.

Edemede a ga-enyere gị aka ịkọwapụta ha.

Ozi niile dị ebe a dị na ịntanetị n'ebe dị iche iche, a na-ekesa ya nke ukwuu! Achọrọ m itinye ihe niile ọnụ - site na DBCC PAGE ruo hobt_id yana ruo ọrụ %% physloc%% na %%lockres%% enweghị akwụkwọ.

Nke mbụ, ka anyị kwuo maka ichere na mkpọchi PAGE, anyị ga-aga n'ihu na mkpọchi igodo.

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

Ọ bụrụ na ajụjụ gị na-eche na mkpọchi PAGE, SQL Server ga-enye gị adreesị ibe ahụ.

Na-emebi "PAGE: 6:3:70133" anyị ga-enweta:

database_id = 6
data_file_id = 3
ibe_numer = 70133

1.1) Decrypt database_id

Ka anyị chọta aha nchekwa data site na iji ajụjụ:

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

Nke a bụ ọha DB WideWorldIbubata na SQL Server m.

1.2) Na-achọ aha faịlụ data - ọ bụrụ na ị nwere mmasị

Anyị ga-eji data_file_id na nzọụkwụ ọzọ iji chọta aha tebụl. Ị nwere ike ịfefe na nzọụkwụ ọzọ, mana ọ bụrụ na ị nwere mmasị na aha faịlụ ahụ, ị nwere ike ịchọta ya site na ịme ajụjụ na ọnọdụ nke nchekwa data achọtara, dochie data_file_id na ajụjụ a:

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

Na nchekwa data WideWorldImporters nke a bụ faịlụ akpọrọ WWI_UserData ma eweghachi m ya na C:MSSQLDATAWideWorldImporters_UserData.ndf. (Ehee, ị jidere m na-etinye faịlụ na diski sistemụ! Mba! Nke ahụ jọgburu onwe ya).

1.3) Nweta aha ihe ahụ site na DBCC PAGE

Ugbu a, anyị maara na ibe #70133 na faịlụ data 3 bụ nke WorldWideImporters nchekwa data. Anyị nwere ike lelee ọdịnaya dị na ibe a site na iji DBCC PAGE enweghị akwụkwọ yana akara ọkọlọtọ 3604.
Mara: Ọ masịrị m iji DBCC PAGE na nnomi eweghachitere site na ndabere ebe na sava ọzọ, n'ihi na ọ bụ ihe enweghị akwụkwọ. N'ọnọdụ ụfọdụ, ọ nwere ike ime ka a na-emepụta ihe mkpofu (ihe ruru. onye ntụgharị - njikọ ahụ, ọ dị mwute ikwu, edugaghị ebe ọ bụla, mana na-ekpe ikpe site na url, anyị na-ekwu maka ndenye enyo enyo.).

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

N'ịga na nsonaazụ ya, ị nwere ike ịhụ object_id na index_id.

Ọ fọrọ nke nta ka emechaa! Ugbu a ị nwere ike ịchọta tebụl na aha ndeksi site na iji ajụjụ a:

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

Ma ugbu a, anyị na-ahụ na mkpọchi echere nọ na PK_Sales_OrderLines index nke Sales.OrderLines.

Mara: Na SQL Server 2014 na emesia, enwere ike ịhụ aha ihe ahụ site na iji DMO sys.dm_db_database_page_allocations enweghị akwụkwọ. Mana ị ga-ajụrịrị ibe ọ bụla na nchekwa data, nke anaghị adị mma maka nnukwu ọdụ data, yabụ ejiri m DBCC PAGE.

1.4) Ọ ga-ekwe omume ịhụ data dị na ibe nke egbochiri?

Ọfọn, ee. Mana ... ị ji n'aka na ị chọrọ ya n'ezie?
Ọ na-adị nwayọọ ọbụna na obere tebụl. Mana ọ dị mma, yabụ ebe ị gụrụ nke a… ka anyị kwuo maka %% physloc%%!

%% physloc%% bụ mpempe anwansi enweghị akwụkwọ nke na-eweghachi ihe nchọpụta anụ ahụ maka ntinye ọ bụla. ị nwere ike iji %% physloc%% tinyere sys.fn_PhysLocFormatter na SQL Server 2008 na elu.

Ugbu a anyị maara na anyị chọrọ igbachi ibe ahụ na Sales.OrderLines, anyị nwere ike lelee data niile dị na tebụl a, nke echekwara na faịlụ data #3 na ibe #70133, na-eji ajụjụ a:

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

Dị ka m kwuru, ọ na-adị nwayọọ ọbụna na obere tebụl. M gbakwunyere NOLOCK na arịrịọ ahụ n'ihi na anyị enweghị nkwa na data anyị chọrọ ile anya bụ otu ihe ahụ ọ dị mgbe achọpụtara mkpọchi - yabụ anyị nwere ike ịgụ ihe ruru unyi.
Mana, mee ọsọ ọsọ, ajụjụ ahụ weghachiri m otu ahịrị 25 ahụ nke ajụjụ anyị lụrụ ọgụ

Zuru oke maka mkpọchi PAGE. Gịnị ma ọ bụrụ na anyị na-eche mkpọchi igodo?

2) waitresource=“KEY: 6:72057594041991168 (ce52f92a058c)” = Database_Id, HOBT_Id (hash anwansi nwere ike decrypter site na iji %%lockres%% ma ọ bụrụ na ị chọrọ n'ezie)

Ọ bụrụ na ajụjụ gị nwaa igbachi ndekọ n'ime ndeksi wee kpochie ya onwe ya, ị ga-enwe ụdị adreesị dị iche kpamkpam.
Na-agbaji "6: 72057594041991168 (ce52f92a058c)" n'ime akụkụ, anyị ga-enweta:

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

2.1) Decrypt database_id

Nke a na-arụ ọrụ kpọmkwem otu ihe atụ n'elu! Chọta aha nchekwa data site na iji ajụjụ a:

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

N'ọnọdụ m ọ ka bụ otu ihe ahụ DB WideWorldIbubata.

2.2) Decrypt hobt_id

N'ihe dị na nchekwa data achọtara, ịkwesịrị ịme ajụjụ na sys.partitions nwere njikọ abụọ ga-enyere aka chọpụta aha tebụl na ndeksi ...

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

Ọ na-agwa m na arịrịọ a na-echere na Ngwa. Mba mkpọchi site na iji PK_Application_Countries index.

2.3) Ugbu a ntakịrị anwansi%% lockres%% - ọ bụrụ na ịchọrọ ịchọpụta ntinye akpọchiri.

Ọ bụrụ n'ezie na achọrọ m ịma nke ahịrị mkpọchi ahụ dị, enwere m ike ịchọpụta site na ịjụ tebụl n'onwe ya. Anyị nwere ike iji ọrụ %%lockres%% enweghị akwụkwọ iji chọta ntinye dabara na hash anwansi.
Biko mara na ajụjụ a ga-enyocha tebụl dum, na na tebụl buru ibu nke a nwere ike ọ gaghị adị ụtọ ma ọlị:

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

M gbakwunyere NOLOCK (na ndụmọdụ Klaus Aschenbrenner na Twitter) n'ihi na mgbochi nwere ike ịghọ nsogbu. Anyị chọrọ ilele ihe dị ugbu a, ọ bụghịkwa ihe dị ebe ahụ mgbe azụmahịa ahụ malitere - echeghị m na nkwekọ data dị anyị mkpa.
Voila, ndekọ anyị lụrụ ọgụ!

Ekele na ịgụkwu ihe

Anaghị m echeta onye buru ụzọ kọwaa ọtụtụ n'ime ihe ndị a, mana ebe a bụ ọkwa abụọ gbasara ihe ndị kacha nta edekọ nke ị nwere ike ịmasị gị:

Okwu Paul Randal dere %% physloc%% na sys.fn_PhysLocFormatter (dị ka anyị nwere data anyị na ihe atụ mbụ)
Ajụjụ na StackOverflow gbasara na-eji %%lockres%% (dị ka anyị chọtara data na ihe atụ nke abụọ). Otu n'ime azịza na-eduga na post Grant Fritchey gbasara %%lockres%%, nke edere azụ na 2010.

isi: www.habr.com

Decipher Key na Page WaitResource n'ime mkpọchi na mkpọchi