ProHoster > Блог > Gudanarwa > Nuna Maɓalli da Shafin WaitResource a cikin makullai da makullai

Nuna Maɓalli da Shafin WaitResource a cikin makullai da makullai

Idan kuna amfani da rahoton tsarin da aka katange ko tattara jadawali na kulle-kullen da SQL Server ke bayarwa lokaci-lokaci, zaku ci karo da abubuwa kamar haka:

waitresource="SHAFI: 6:3:70133"

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

Wani lokaci, za a sami ƙarin bayani a cikin wannan ƙaton XML ɗin da kuke nazarin (zane-zane na kulle-kulle sun ƙunshi jerin albarkatun da ke taimaka muku gano sunayen abu da fihirisa), amma ba koyaushe ba.

Wannan rubutun zai taimaka muku gano su.

Duk bayanan da ke nan suna kan Intanet a wurare daban-daban, kawai ana rarraba su sosai! Ina so in haɗa shi duka, daga DBCC PAGE zuwa hobt_id zuwa ayyukan %% physloc%% da %%lockres%% mara izini.

Da farko, bari mu yi magana game da jira akan makullai PAGE, sannan mu matsa zuwa makullin KEY.

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

Idan buƙatarku tana jira akan makullin PAGE, SQL Server zai ba ku adireshin wannan shafin.

Watsewa "SHAFIN: 6:3:70133" muna samun:

  • database_id = 6
  • data_file_id = 3
  • shafi_lambar = 70133

1.1) Decrypt database_id

Nemo sunan rumbun adana bayanai ta amfani da tambayar:

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

Jama'a ne DB WideWorldImporters a kan SQL Server dina.

1.2) Neman sunan fayil ɗin bayanai - idan kuna sha'awar

Za mu yi amfani da data_file_id a mataki na gaba don nemo sunan tebur. Za ku iya tsallake zuwa mataki na gaba kawai, amma idan kuna sha'awar sunan fayil, zaku iya nemo shi ta hanyar gudanar da tambaya a cikin mahallin bayanan da aka samo, musanya data_file_id cikin wannan tambayar:

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

A cikin bayanan WideWorldImporters wannan fayil ne mai suna WWI_UserData kuma na mayar da shi zuwa C:MSSQLDATAWideWorldImporters_UserData.ndf. (Kash, kun kama ni ina saka fayiloli akan faifan tsarin! A'a! Abin kunya ne).

1.3) Samo sunan abu daga DBCC PAGE

Yanzu mun san cewa shafi na #70133 a cikin datafile 3 na cikin bayanan WorldWideImporters. Za mu iya duba abubuwan da ke cikin wannan shafi ta amfani da DBCC PAGE mara izini da alamar tuta 3604.
Lura: Na fi so in yi amfani da DBCC PAGE akan kwafin da aka dawo da shi a wani wuri akan wata uwar garken, saboda kayan da ba su da izini. A wasu lokuta, ta na iya haifar da juji (kusan mai fassara - hanyar haɗin gwiwa, da rashin alheri, ba ta kai ko'ina ba, amma kuna yin hukunci da url, muna magana ne game da fitattun fitattun bayanai.).

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

Ta gungurawa zuwa sakamako, zaku iya nemo object_id da index_id.
Nuna Maɓalli da Shafin WaitResource a cikin makullai da makullai
Kusan an gama! Yanzu zaku iya nemo tebur da sunayen fihirisa tare da tambaya:

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

Kuma a nan mun ga cewa jira a kan kulle yana kan PK_Sales_OrderLines index na Sales.OrderLines.

Lura: A cikin SQL Server 2014 da sama, ana kuma iya samun sunan abu ta amfani da DMO sys.dm_db_database_page_allocations mara izini. Amma dole ne ka tambayi kowane shafi a cikin ma'ajin bayanai, wanda ba ya da kyau ga manyan bayanai, don haka na yi amfani da DBCC PAGE.

1.4) Shin zai yiwu a ga bayanan da ke shafin da aka toshe?

Nuu, iya. Amma… ka tabbata da gaske kana bukata?
Yana da sannu ko da akan ƙananan teburi. Amma yana da kyau, don haka tun da kun karanta wannan zuwa yanzu ... bari muyi magana game da %% physloc%%!

%% physloc%% sihiri ne mara izini wanda ke dawo da ID na zahiri don kowace shigarwa. zaka iya amfani %% physloc%% tare da sys.fn_PhysLocFormatter a cikin SQL Server 2008 da kuma daga baya.

Yanzu da muka san cewa muna so mu toshe shafin a Sales.OrderLines, za mu iya duba duk bayanan da ke cikin wannan tebur, wanda aka adana a cikin fayil na #3 akan shafi #70133, tare da tambaya mai zuwa:

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

Kamar yadda na ce, yana da hankali ko da akan ƙananan teburi. Na kara NOLOCK ga bukatar saboda har yanzu ba mu da tabbacin cewa bayanan da muke so mu duba daidai suke da daidai lokacin da aka gano makullin - don haka za mu iya yin karatun datti.
Amma, hooray, tambayar ta dawo mini da layuka 25 waɗanda tambayarmu ta yi yaƙi don su.
Nuna Maɓalli da Shafin WaitResource a cikin makullai da makullai
Ya isa game da makullin PAGE. Idan muna jiran makullin KEY fa?

2) waitresource = "KEY: 6:72057594041991168 (ce52f92a058c)" = Database_Id, HOBT_Id (zaton sihiri wanda za'a iya yankewa tare da %% lockres%% idan da gaske kuna son)

Idan tambayarka tayi ƙoƙarin kullewa akan shigarwar fihirisa kuma ta kulle kanta, zaka sami wani nau'in adireshi mabambanta.
Karɓar "6:72057594041991168 (ce52f92a058c)" zuwa sassa, muna samun:

  • database_id = 6
  • hobt_id = 72057594041991168
  • sihiri zanta = (ce52f92a058c)

2.1) Decrypt database_id

Yana aiki daidai da misalin da ke sama! Muna samun sunan rumbun adana bayanai ta amfani da tambayar:

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

A wurina ma haka ne DB WideWorldImporters.

2.2) Decrypt hobt_id

A cikin mahallin bayanan da aka samo, kuna buƙatar tambayar sys.partitions tare da haɗin gwiwa biyu waɗanda zasu taimaka tantance sunayen tebur da fihirisa ...

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

Ya gaya mani cewa buƙatar tana jira akan Aikace-aikacen. Ƙasashe suna kulle ta amfani da fihirisar PK_Application_Countries.

2.3) Yanzu ga wasu %% lockres%% sihiri - idan kuna son gano wane shigarwa aka kulle

Idan da gaske ina son sanin akan wane layi ake buƙatar makullin, zan iya ganowa ta hanyar tambayar tebur ɗin kanta. Za mu iya amfani da aikin %%lockres%% mara izini don nemo shigarwar da ta yi daidai da zaren sihirin.
Lura cewa wannan tambayar za ta duba dukkan teburin, kuma akan manyan tebura wannan bazai yi daɗi ba kwata-kwata:

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

Na kara NOLOCK (akan shawarar Klaus Aschenbrenner akan twitter) saboda makulli na iya zama matsala. Muna so mu kalli abin da yake a yanzu, kuma ba abin da yake can ba lokacin da aka fara ciniki - Ba na tsammanin cewa daidaiton bayanai yana da mahimmanci a gare mu.
Voila, rikodin da muka yi yaƙi!
Nuna Maɓalli da Shafin WaitResource a cikin makullai da makullai

Godiya da kara karatu

Ban tuna wanda ya fara bayyana yawancin waɗannan abubuwan ba, amma a nan akwai rubutu guda biyu game da mafi ƙarancin rubuce-rubucen abubuwan da kuke so:

source: www.habr.com

Add a comment