Afai e te faʻaogaina le lipoti o le faʻaogaina o le faʻagasologa poʻo le aoina o kalafi faʻamaʻi e tuʻuina mai e le SQL Server i lea taimi ma lea taimi, o le ae feagai ma mea faʻapenei:
waitresource=“IUULAU: 6:3:70133”
waitresource=“KEY: 6:72057594041991168 (ce52f92a058c)“
O nisi taimi o le a tele faʻamatalaga i lena XML tele o loʻo e suʻesuʻeina (kalafa faʻamaʻi o loʻo i ai se lisi o punaoa e fesoasoani ia te oe e suʻe ai le mea faitino ma igoa faʻasino), ae le o taimi uma.
O le a fesoasoani lenei tusitusiga ia te oe e faauigaina.
O faʻamatalaga uma o loʻo i ai iinei o loʻo i luga o le Initaneti i nofoaga eseese, ua naʻo le tufatufaina atu! Ou te manaʻo e tuʻu faʻatasi mea uma - mai le DBCC PAGE i le hobt_id ma le %%physloc%% ma le %%lockres%% galuega e leai ni faʻamaumauga.
Muamua, se'i o tatou talanoa e uiga i le fa'atalitali i loka ITULAU, ona tatou aga'i atu ai lea ile loka KEY.
1) waitresource=“IUULAU: 6:3:70133” = Database_Id: FileId: PageNumber
Afai o loʻo faʻatali lau fesili ile loka ITULAU, ole SQL Server o le a tuʻuina atu ia te oe le tuatusi o lena itulau.
O le talepeina i lalo "ITUU: 6: 3: 70133" matou te maua:
- database_id = 6
- data_file_id = 3
- itulau_numera = 70133
1.1) Decrypt database_id
Se'i tatou su'e le igoa fa'amaumauga e fa'aaoga ai le fesili:
SELECT
name
FROM sys.databases
WHERE database_id=6;
GOO le lautele lea i luga o laʻu SQL Server.
1.2) Su'e le igoa o le faila faila - pe afai e te fiafia
O le a matou faʻaogaina data_file_id i le isi laasaga e suʻe ai le igoa laulau. E mafai ona e faaseʻe i le isi laasaga, ae afai e te fiafia i le igoa o le faila, e mafai ona e mauaina e ala i le faʻaogaina o se fesili i le tulaga o le database na maua, sui data_file_id i lenei fesili:
USE WideWorldImporters;
GO
SELECT
name,
physical_name
FROM sys.database_files
WHERE file_id = 3;
GOI le WideWorldImporters database o le faila lea e ta'ua WWI_UserData ma ua ou toe fa'afoisia i le C:MSSQLDATAWideWorldImporters_UserData.ndf. (Oi, na e mauaina aʻu o loʻo tuʻuina faila i luga o le disk system! Leai! Sa faʻafefe).
1.3) Maua le igoa mea mai le DBCC PAGE
O lea ua matou iloa o le itulau #70133 i le faila faila 3 o lo'o i le WorldWideImporters database. E mafai ona tatou vaʻavaʻai i mea o loʻo i totonu o lenei itulau e faʻaaoga ai le DBCC PAGE e le faʻamaonia ma suʻe le fuʻa 3604.
Manatua: Ou te manaʻo e faʻaoga le DBCC PAGE i luga o se kopi toe faʻaleleia mai se faʻamaumauga i se mea i luga o se isi server, aua o se mea e le faʻamaonia. I nisi tulaga, o ia (tusa. fa'aliliu - o le feso'ota'iga, o le mea e leaga ai, e leai se mea e ta'ita'ia ai, ae fa'amasino i le url, o lo'o tatou talanoa e uiga i fa'asino igoa).
/* 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
Fa'asaga i fa'ai'uga, e mafai ona e mauaina mea_id ma index_id.
Toetoe lava uma! O lea e mafai ona e mauaina le laulau ma fa'asino igoa e fa'aaoga ai le fesili:
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;
GOMa o lea ua tatou va'ai o le loka fa'atali sa i luga o le PK_Sales_OrderLines index o le Sales.OrderLines laulau.
Fa'aaliga: I le SQL Server 2014 ma mulimuli ane, e mafai fo'i ona maua le igoa mea e fa'aaoga ai le DMO sys.dm_db_database_page_allocations e le'i fa'amaonia. Ae e tatau ona e fesiligia itulau uma i totonu o faʻamaumauga, lea e le foliga manaia tele mo faʻamaumauga tetele, o lea na ou faʻaogaina ai le DBCC PAGE.
1.4) E mafai ona vaʻaia faʻamatalaga i luga o le itulau na poloka?
Ia, ioe. Ae... e te mautinoa e te manaʻomia moni lava?
E telegese e oo lava i luga o laulau laiti. Ae e ese le manaia, o lea talu ai ua uma ona e faitau i le taimi nei... ta talanoa e uiga i %%physloc%%!
%%physloc%% ose togafiti fa'ataulāitu e le'i fa'amauina e toe fa'afo'i mai ai se fa'amatalaga fa'aletino mo fa'amatalaga ta'itasi. e mafai ona e faaaogaina .
I le taimi nei ua matou iloa na matou mananaʻo e loka le itulau i Sales.OrderLines, e mafai ona matou vaʻavaʻai i faʻamatalaga uma i lenei laulau, o loʻo teuina i le faila faʻamaumauga #3 i le itulau #70133, faʻaaoga lenei fesili:
Use WideWorldImporters;
GO
SELECT
sys.fn_PhysLocFormatter (%%physloc%%),
*
FROM Sales.OrderLines (NOLOCK)
WHERE sys.fn_PhysLocFormatter (%%physloc%%) like '(3:70133%'
GOE pei ona ou fai atu, e telegese e oo lava i luga o laulau laiti. Na ou faʻaopoopoina le NOLOCK i le talosaga aua e leai lava se matou faʻamautinoaga o faʻamaumauga matou te fia vaʻai i ai e tutusa lelei lava ma le taimi na iloa ai le loka - ina ia mafai ona matou faia ma le saogalemu le faitau palapala.
Ae, vave, o le fesili e toe faafoi mai ia te au laina tutusa e 25 na tau ai le matou fesili
Ua lava e uiga i loka ITULAU. Ae faapefea pe a tatou faatali mo se loka KI?
2) waitresource = "KEY: 6: 72057594041991168 (ce52f92a058c)" = Database_Id, HOBT_Id (faʻailoga faʻapitoa e mafai ona faʻaaogaina e faʻaaoga ai le%%lockres%% pe a e manaʻo moni i ai)
Afai e taumafai lau su'esu'ega e loka se fa'amaumauga i totonu o le fa'asinomaga ma loka ia lava, e te i'u i se ituaiga tuatusi e matua'i ese lava.
Tuʻu "6:72057594041991168 (ce52f92a058c)" i ni vaega, matou te maua:
- database_id = 6
- hobt_id = 72057594041991168
- fa'ailoga fa'ailoga = (ce52f92a058c)
2.1) Decrypt database_id
E tutusa lelei lava ma le fa'ata'ita'iga o lo'o i luga! Su'e le igoa fa'amaumauga e fa'aoga ai le fesili:
SELECT
name
FROM sys.databases
WHERE database_id=6;
GOI lo'u tulaga e tutusa lava .
2.2) Fa'asa'o fa'ailoga hobt_id
I le tulaga o le faʻamaumauga o loʻo maua, e tatau ona e faʻatinoina se fesili i sys.partitions faʻatasi ai ma se paga o faʻatasi o le a fesoasoani e fuafua igoa o le laulau ma faʻasino ...
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;
GOO lo'o ta'u mai ai o lo'o fa'atali le talosaga ile loka Application.Countries e fa'aaoga ai le PK_Application_Countries index.
2.3) O lea la o sina togafiti fa'ataulāitu %%lockres%% - pe a e mana'o e su'e po'o fea mea na lokaina
Afai ou te fia iloa po o fea le laina sa i ai le loka, e mafai ona ou iloa e ala i le fesiligia o le laulau lava ia. E mafai ona matou fa'aogaina le %%lockres%% galuega e le'i fa'amauina e su'e ai se fa'amatalaga e fetaui ma le fa'amaneta.
Faamolemole ia matau o lenei fesili o le a suʻesuʻeina le laulau atoa, ma luga o laulau lapopoa atonu e le malie tele:
SELECT
*
FROM Application.Countries (NOLOCK)
WHERE %%lockres%% = '(ce52f92a058c)';
GO
Na ou faaopoopo le NOLOCK () ona o poloka poloka e mafai ona avea ma faʻafitauli. Matou te fia vaʻavaʻai i mea o loʻo i ai nei, ae le o le mea na i ai ina ua amata le fefaʻatauaiga - Ou te le manatu e taua ia i matou le tutusa o faʻamatalaga.
Voila, o le faamaumauga na tatou tau ai!
Fa'afetai ma faitau atili
Ou te le manatua po o ai na muamua faamatalaina le tele o nei mea, ae o pou nei e lua e uiga i mea e sili ona faʻamaonia e te ono manaʻo ai:
- Paul Randal's post about (e pei ona i ai a matou faʻamatalaga i le faʻataʻitaʻiga muamua)
- Fesili ile StackOverflow e uiga i (e pei ona matou mauaina faʻamatalaga i le faʻataʻitaʻiga lona lua). O se tasi o tali e tau atu i se pou .
puna: www.habr.com