Inā hoʻohana ʻoe i ka hōʻike kaʻina hana i hoʻopaʻa ʻia a hōʻiliʻili paha i nā kiʻi make i hāʻawi ʻia e SQL Server i kēlā me kēia manawa, e hālāwai ʻoe i nā mea e like me kēia:
waitresource="PAGE: 6:3:70133"
waitresource=“KEY: 6:72057594041991168 (ce52f92a058c)“
I kekahi manawa, e nui aʻe ka ʻike i loko o kēlā XML nunui āu e aʻo ai (ʻo nā kiʻi deadlock kahi papa inoa o nā kumuwaiwai e kōkua iā ʻoe e ʻike i nā inoa o ka mea a me ka index), akā ʻaʻole mau.
E kōkua kēia kikokikona iā ʻoe e wehewehe iā lākou.
Aia nā ʻike a pau ma ka Pūnaewele ma nā wahi like ʻole, hoʻolaha wale ʻia! Makemake au e hui pū, mai DBCC PAGE a hobt_id i nā hana %%physloc%% a me %%lockres%% i palapala ʻole ʻia.
ʻO ka mua, e kamaʻilio e pili ana i nā kali ma nā laka PAGE, a laila neʻe i nā laka KEY.
1) waitresource="PAGE: 6:3:70133" = Database_Id: FileId: PageNumber
Inā e kali ana kāu noi ma kahi laka PAGE, hāʻawi ʻo SQL Server iā ʻoe i ka helu wahi o kēlā ʻaoʻao.
Ke wāwahi nei i ka "PAE: 6:3:70133" loaʻa iā mākou:
- waihona_id = 6
- ʻikepili_file_id = 3
- helu_aoao = 70133
1.1) Decrypt database_id
E ʻimi i ka inoa o ka waihona me ka hoʻohana ʻana i ka nīnau:
SELECT
name
FROM sys.databases
WHERE database_id=6;
GOHe lehulehu ma kaʻu kikowaena SQL.
1.2) Ke ʻimi nei i ka inoa o ka faila data - inā makemake ʻoe
E hoʻohana ana mākou i data_file_id i ka ʻanuʻu aʻe e ʻike ai i ka inoa papa. Hiki iā ʻoe ke lele i ka ʻanuʻu aʻe, akā inā makemake ʻoe i ka inoa faila, hiki iā ʻoe ke loaʻa ma ka holo ʻana i kahi nīnau ma ka pōʻaiapili o ka waihona i loaʻa, e hoʻololi ana i ka data_file_id i kēia nīnau:
USE WideWorldImporters;
GO
SELECT
name,
physical_name
FROM sys.database_files
WHERE file_id = 3;
GOMa ka waihona WideWorldImporters he waihona kēia i kapa ʻia WWI_UserData a ua hoʻihoʻi ʻia iaʻu i C:MSSQLDATAWideWorldImporters_UserData.ndf. (ʻAe, ua hopu ʻoe iaʻu e kau ana i nā faila ma ka ʻōnaehana ʻōnaehana! ʻAʻole! He mea hilahila).
1.3) E kiʻi i ka inoa mea mai DBCC PAGE
Ua ʻike mākou i kēia manawa ʻo ka ʻaoʻao #70133 ma ka datafile 3 aia i ka waihona WorldWideImporters. Hiki iā mākou ke nānā i nā mea o kēia ʻaoʻao me ka hoʻohana ʻana i ka palapala DBCC PAGE a me ka hae hae 3604.
'Ōlelo Aʻo: Makemake au e hoʻohana i ka DBCC PAGE ma kahi kope i hoʻihoʻi ʻia ma kahi o kahi kikowaena ʻē aʻe, no ka mea, he mea palapala ʻole ia. I kekahi mau hihia, ʻo ia (kokoke. unuhi - ʻo ka loulou, akā naʻe, ʻaʻole i alakaʻi i kahi, akā i ka hoʻoholo ʻana ma ka url, ke kamaʻilio nei mākou e pili ana i nā index kānana.).
/* 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
Ma ka hele ʻana i nā hopena, hiki iā ʻoe ke loaʻa ka object_id a me index_id.
Kokoke pau! I kēia manawa hiki iā ʻoe ke ʻimi i ka papaʻaina a me nā inoa kuhikuhi me kahi nīnau:
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;
GOA ma ʻaneʻi mākou e ʻike ai aia ka kali ma ka laka ma ka papa kuhikuhi PK_Sales_OrderLines o ka papa ʻaina Sales.OrderLines.
'Ōlelo Aʻo: Ma SQL Server 2014 a ma luna, hiki ke loaʻa ka inoa mea me ka hoʻohana ʻana i ka DMO sys.dm_db_database_page_allocations i palapala ʻole ʻia. Akā, pono ʻoe e nīnau i kēlā me kēia ʻaoʻao i loko o ka waihona, ʻaʻole i ʻike maikaʻi ʻia no nā ʻikepili nui, no laila ua hoʻohana wau i ka DBCC PAGE.
1.4) Hiki ke ʻike i ka ʻikepili ma ka ʻaoʻao i pāpā ʻia?
Nuuu, ae. Akā… ʻoiaʻiʻo paha ʻoe makemake maoli ia?
Ua lohi ma nā papa liʻiliʻi. Akā, ʻano ʻoluʻolu, no laila, ʻoiai ua heluhelu ʻoe i kēia mamao...e kamaʻilio kāua no %%physloc%%!
ʻO %%physloc%% kahi ʻāpana kupua ʻole i hoʻihoʻi i kahi ID kino no kēlā me kēia komo. hiki iā ʻoe ke hoʻohana .
I kēia manawa ua ʻike mākou ua makemake mākou e ālai i ka ʻaoʻao ma Sales.OrderLines, hiki iā mākou ke nānā i nā ʻikepili a pau ma kēia pākaukau, i mālama ʻia ma ka waihona ʻikepili #3 ma ka ʻaoʻao #70133, me kēia nīnau:
Use WideWorldImporters;
GO
SELECT
sys.fn_PhysLocFormatter (%%physloc%%),
*
FROM Sales.OrderLines (NOLOCK)
WHERE sys.fn_PhysLocFormatter (%%physloc%%) like '(3:70133%'
GOE like me kaʻu i ʻōlelo ai, ua lohi ma nā papa liʻiliʻi. Ua hoʻohui au iā NOLOCK i ka noi no ka mea ʻaʻohe o mākou hōʻoiaʻiʻo ʻo ka ʻikepili a mākou e makemake ai e nānā e like me ka manawa i ʻike ʻia ai ka laka - no laila hiki iā mākou ke hana palekana i nā heluhelu lepo.
Akā, hooray, hoʻihoʻi mai ka nīnau iaʻu i nā lālani he 25 i hakakā ai kā mākou nīnau.
Ua lawa e pili ana i nā laka PAGE. He aha inā mākou e kali nei i kahi laka KEY?
2) waitresource = "KEY: 6:72057594041991168 (ce52f92a058c)" = Database_Id, HOBT_Id (magic hash hiki ke hoʻokaʻawale ʻia me %%lockres%% inā makemake ʻoe)
Inā ho'āʻo kāu nīnau e laka i kahi helu helu helu a hoʻopaʻa ʻia iā ia iho, loaʻa iā ʻoe kahi ʻano helu ʻokoʻa.
Wahi a "6:72057594041991168 (ce52f92a058c)" i mau ʻāpana, loaʻa iā mākou:
- waihona_id = 6
- hobt_id = 72057594041991168
- magic hash = (ce52f92a058c)
2.1) Decrypt database_id
Hana like ia me ka laʻana ma luna! ʻIke mākou i ka inoa o ka waihona me ka hoʻohana ʻana i ka nīnau:
SELECT
name
FROM sys.databases
WHERE database_id=6;
GOI koʻu hihia, ua like ia .
2.2) Wehewehe hobt_id
I loko o ka pōʻaiapili o ka ʻikepili i loaʻa, pono ʻoe e nīnau i nā sys.partitions me nā hui ʻelua e kōkua i ka hoʻoholo ʻana i nā inoa o ka papa a me ka index ...
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;
GOHōʻike mai iaʻu e kali ana ka noi ma ka laka Application.Countries me ka hoʻohana ʻana i ka PK_Application_Countries index.
2.3) I kēia manawa no kekahi %%lockres%% magic - inā makemake ʻoe e ʻike i ke komo ʻana i laka ʻia
Inā makemake au e ʻike i ka lālani i makemake ʻia ai ka laka, hiki iaʻu ke ʻike ma ka nīnau ʻana i ka pākaukau ponoʻī. Hiki iā mākou ke hoʻohana i ka hana %%lockres%% ʻaʻole i palapala ʻia e ʻimi i kahi komo e pili ana i ka hash magic.
E hoʻomanaʻo e nānā kēia nīnau i ka papaʻaina holoʻokoʻa, a ma nā papa nui ʻaʻole paha he leʻaleʻa kēia:
SELECT
*
FROM Application.Countries (NOLOCK)
WHERE %%lockres%% = '(ce52f92a058c)';
GO
Ua hoʻohui au iā NOLOCK () no ka mea hiki ke pilikia nā laka. Makemake wale mākou e nānā i nā mea i laila i kēia manawa, ʻaʻole i ka mea i laila i ka wā i hoʻomaka ai ke kālepa - ʻaʻole wau e manaʻo he mea nui ka ʻikepili ʻikepili iā mākou.
Voila, ka moʻolelo a mākou i hakakā ai!
Mahalo a heluhelu hou aku
ʻAʻole wau e hoʻomanaʻo i ka mea nāna i wehewehe mua i kēia mau mea, akā eia nā pou ʻelua e pili ana i nā mea liʻiliʻi i kākau ʻia āu e makemake ai:
- Paul Randal's post about (e like me kā mākou ʻikepili i ka laʻana mua)
- Nīnau ma StackOverflow e pili ana (e like me kā mākou i ʻike ai i ka ʻikepili ma ka laʻana ʻelua). ʻO kekahi o nā pane e alakaʻi i ka pou .
Source: www.habr.com