ProHoster > Блог > Isakoso > Bọtini Decrypting ati Oju-iwe WaitResource ni awọn titiipa ati awọn titiipa

Bọtini Decrypting ati Oju-iwe WaitResource ni awọn titiipa ati awọn titiipa

Ti o ba lo ijabọ ilana ti dina mọ tabi gba awọn aworan titiipa ti a pese nipasẹ SQL Server lorekore, iwọ yoo pade awọn nkan bii eyi:

waitresource=“ OJẸ: 6:3:70133“

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

Nigba miiran alaye diẹ sii yoo wa ninu XML nla ti o nkọ (awọn aworan ipari ni atokọ ti awọn orisun ti o ṣe iranlọwọ fun ọ lati wa nkan naa ati awọn orukọ atọka), ṣugbọn kii ṣe nigbagbogbo.

Ọrọ yii yoo ṣe iranlọwọ fun ọ lati pinnu wọn.

Gbogbo alaye ti o wa nibi wa lori Intanẹẹti ni awọn aye pupọ, o kan pin kaakiri! Mo fẹ lati fi ohun gbogbo papọ - lati DBCC PAGE si hobt_id ati si awọn iṣẹ%% physloc%% ti ko ni iwe-aṣẹ ati %%lockres%% awọn iṣẹ.

Ni akọkọ, jẹ ki a sọrọ nipa awọn iduro lori awọn titiipa PAGE, ati lẹhinna a yoo lọ si awọn titiipa bọtini.

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

Ti ibeere rẹ ba nduro lori titiipa PAGE, SQL Server yoo fun ọ ni adirẹsi oju-iwe yẹn.

Pipalẹ "OJU: ​​6: 3: 70133" a gba:

  • database_id = 6
  • data_file_id = 3
  • page_numer = 70133

1.1) Decrypt database_id

Jẹ ki a wa orukọ data data nipa lilo ibeere:

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

Eleyi jẹ àkọsílẹ DB WideWorldIgbewọle lori olupin SQL mi.

1.2) Wiwa fun orukọ faili data - ti o ba nifẹ

A yoo lo data_file_id ni igbesẹ ti nbọ lati wa orukọ tabili naa. O le nirọrun foju si igbesẹ ti n tẹle, ṣugbọn ti o ba nifẹ si orukọ faili, o le rii nipasẹ ṣiṣe ibeere kan ni aaye aaye data ti o rii, rọpo data_file_id ninu ibeere yii:

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

Ninu aaye data WideWorldImporters eyi jẹ faili ti a pe ni WWI_UserData ati pe Mo ni pada si C:MSSQLDATAWideWorldImporters_UserData.ndf. (Yeee, o mu mi fifi awọn faili sori disk eto! Rara! Iyẹn buruju).

1.3) Gba orukọ ohun lati DBCC PAGE

Bayi a mọ pe oju-iwe #70133 ninu faili data 3 jẹ ti aaye data WorldWideImporters. A le wo awọn akoonu inu oju-iwe yii nipa lilo DBCC PAGE ti ko ni iwe-aṣẹ ati itọpa asia 3604.
Akiyesi: Mo fẹ lati lo DBCC PAGE lori ẹda ti o tun pada lati afẹyinti ni ibikan lori olupin miiran, nitori pe o jẹ ohun ti ko ni iwe-aṣẹ. Ni awọn igba miiran, o le ja si ni a idalẹnu ni a ṣẹda (isunmọ. onitumọ - ọna asopọ, laanu, ko yorisi nibikibi, ṣugbọn idajọ nipasẹ url, a n sọrọ nipa awọn atọka ti a yan).

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

Yi lọ nipasẹ awọn abajade, o le wa object_id ati index_id.
Bọtini Decrypting ati Oju-iwe WaitResource ni awọn titiipa ati awọn titiipa
O fẹrẹ ṣe! Bayi o le wa tabili ati awọn orukọ atọka nipa lilo ibeere naa:

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

Ati nisisiyi a rii pe idaduro titiipa wa lori itọka PK_Sales_OrderLines ti tabili Sales.OrderLines.

Akiyesi: Ni SQL Server 2014 ati nigbamii, orukọ ohun naa tun le rii ni lilo DMO ti ko ni iwe-aṣẹ sys.dm_db_database_page_allocations. Ṣugbọn o ni lati beere gbogbo oju-iwe ti o wa ninu ibi ipamọ data, eyiti ko dara pupọ fun awọn apoti isura data nla, nitorinaa Mo lo DBCC PAGE.

1.4) Ṣe o ṣee ṣe lati wo data lori oju-iwe ti o dina?

O dara, bẹẹni. Ṣugbọn... ṣe o da ọ loju pe o nilo rẹ gaan?
O lọra paapaa lori awọn tabili kekere. Ṣugbọn o dara pupọ, nitorinaa niwon o ti ka eyi jina… jẹ ki a sọrọ nipa %% physloc%%!

%% physloc%% jẹ idan ti ko ni iwe-aṣẹ ti o da idamo ti ara pada fun titẹ sii kọọkan. o le lo %% physloc%% pẹlu sys.fn_PhysLocFormatter ni SQL Server 2008 ati ga julọ.

Ni bayi ti a mọ pe a fẹ lati tii oju-iwe naa ni Sales.OrderLines, a le wo gbogbo data ti o wa ninu tabili yii, eyiti o fipamọ sinu faili data #3 ni oju-iwe #70133, ni lilo ibeere yii:

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

Bi mo ti sọ, o lọra paapaa lori awọn tabili kekere. Mo ṣafikun NOLOCK si ibeere naa nitori a ko ni iṣeduro pe data ti a fẹ lati wo jẹ deede kanna bi o ti jẹ nigbati a ti rii titiipa - nitorinaa a le ṣe awọn kika idọti lailewu.
Ṣugbọn, yara, ibeere naa da mi pada awọn ila 25 kanna ti ibeere wa ja fun
Bọtini Decrypting ati Oju-iwe WaitResource ni awọn titiipa ati awọn titiipa
To nipa awọn titiipa PAGE. Ti a ba n duro de titiipa bọtini kan nko?

2) waitresource=“KỌ́KỌ́: 6:72057594041991168 (ce52f92a058c)” = Database_Id, HOBT_Id (hash magic ti o le sọkuro ni lilo %%lockres%% ti o ba fẹ iyẹn gaan)

Ti ibeere rẹ ba gbiyanju lati tii igbasilẹ kan ninu atọka ati tiipa funrararẹ, o pari pẹlu iru adirẹsi ti o yatọ patapata.
Pipa “6:72057594041991168 (ce52f92a058c)” sinu awọn apakan, a gba:

  • database_id = 6
  • hobt_id = 72057594041991168
  • idan elile = (ce52f92a058c)

2.1) Decrypt database_id

Eleyi ṣiṣẹ gangan kanna bi awọn apẹẹrẹ loke! Wa orukọ data data nipa lilo ibeere:

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

Ninu ọran mi o tun jẹ kanna DB WideWorldIgbewọle.

2.2) Decrypt hobt_id

Ni aaye ti ibi ipamọ data ti o rii, o nilo lati ṣiṣẹ ibeere kan si sys.partitions pẹlu awọn akojọpọ meji ti yoo ṣe iranlọwọ lati pinnu awọn orukọ tabili ati atọka…

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

O sọ fun mi pe ibeere naa nduro lori Ohun elo. Awọn orilẹ-ede tiipa ni lilo atọka PK_Application_Countries.

2.3) Bayi idan kekere kan %% lockres%% - ti o ba fẹ wa iru titẹ sii ti o tiipa.

Ti Mo ba fẹ gaan lati mọ iru ọna ti titiipa naa wa, Mo le rii nipa bibeere tabili funrararẹ. A le lo iṣẹ %%lockres%% ti ko ni iwe-aṣẹ lati wa titẹ sii ti o baamu hash idan.
Jọwọ ṣe akiyesi pe ibeere yii yoo ṣayẹwo gbogbo tabili naa, ati lori awọn tabili nla eyi le ma jẹ igbadun rara:

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

Mo fi NOLOCK kun (lori imọran ti Klaus Aschenbrenner lori Twitter) nitori awọn idena le di iṣoro. A kan fẹ lati wo ohun ti o wa ni bayi, kii ṣe ohun ti o wa nigbati idunadura naa bẹrẹ - Emi ko ro pe aitasera data ṣe pataki fun wa.
Voila, igbasilẹ ti a ja fun!
Bọtini Decrypting ati Oju-iwe WaitResource ni awọn titiipa ati awọn titiipa

Awọn iyin ati kika siwaju sii

Emi ko ranti ẹni ti o kọkọ ṣapejuwe ọpọlọpọ awọn nkan wọnyi, ṣugbọn nibi ni awọn ifiweranṣẹ meji nipa awọn nkan ti o kere ju ti o ni akọsilẹ ti o le fẹ:

orisun: www.habr.com

Fi ọrọìwòye kun