Ngati mugwiritsa ntchito lipoti lotsekeredwa kapena kusonkhanitsa ma graph omwe amaperekedwa ndi SQL Server nthawi ndi nthawi, mudzakumana ndi zinthu monga izi:
waitresource="TSAMBA: 6:3:70133"
waitresource=βKEY: 6:72057594041991168 (ce52f92a058c)β
Nthawi zina, padzakhala zambiri mu XML yaikuluyi yomwe mumaphunzira (ma graph of deadlock ali ndi mndandanda wazinthu zomwe zimakuthandizani kudziwa mayina a chinthucho ndi index), koma osati nthawi zonse.
Lemba ili likuthandizani kuwamasulira.
Zonse zomwe zili pano zili pa intaneti m'malo osiyanasiyana, zimafalitsidwa kwambiri! Ndikufuna kuziyika zonse, kuchokera ku DBCC PAGE kupita ku hobt_id kupita ku %%physloc%% ndi %%lockres%% zosalembedwa.
Choyamba, tiyeni tikambirane za kudikirira pa maloko a PAGE, kenako kupita ku KEY maloko.
1) waitresource="PAGE: 6:3:70133" = Database_Id: FileId: PageNumber
Ngati pempho lanu likudikirira pa loko ya PAGE, SQL Server ikupatsani adilesi ya tsambalo.
Kufotokozera "TSAMBA: 6:3:70133" timapeza:
- database_id = 6
- data_file_id = 3
- tsamba_nambala = 70133
1.1) Decrypt database_id
Pezani dzina la database pogwiritsa ntchito funso:
SELECT
name
FROM sys.databases
WHERE database_id=6;
GONdi zapagulu pa SQL Server yanga.
1.2) Kuyang'ana dzina la fayilo ya data - ngati mukufuna
Tigwiritsa ntchito data_file_id mu sitepe yotsatira kuti tipeze dzina la tebulo. Mutha kungolumphira ku sitepe yotsatira, koma ngati mukufuna dzina la fayilo, mutha kulipeza pofunsa funso lomwe lili patsamba lomwe lapezeka, ndikulowetsa data_file_id mufunso ili:
USE WideWorldImporters;
GO
SELECT
name,
physical_name
FROM sys.database_files
WHERE file_id = 3;
GOMunkhokwe ya WideWorldImporters iyi ndi fayilo yotchedwa WWI_UserData ndipo ndili nayo yobwezeretsedwa ku C:MSSQLDATAWideWorldImporters_UserData.ndf. (Oops, mwandigwira ndikuyika mafayilo pa system drive! Ayi! Ndizochititsa manyazi).
1.3) Pezani dzina lachinthu kuchokera ku DBCC PAGE
Tsopano tikudziwa kuti tsamba #70133 mu datafile 3 ndi la WorldWideImporters database. Titha kuyang'ana zomwe zili patsambali pogwiritsa ntchito DBCC PAGE yosalembedwa ndikutsata mbendera 3604.
Zindikirani: Ndimakonda kugwiritsa ntchito DBCC PAGE pa kopi yobwezeretsedwa kwinakwake pa seva ina, chifukwa ndi zinthu zosalembedwa. Nthawi zina, iye (pafupifupi. womasulira - ulalo, mwatsoka, supita kulikonse, koma kuweruza ndi url, tikulankhula za indexes osasankhidwa.).
/* 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
Poyang'ana pazotsatira, mutha kupeza object_id ndi index_id.
Watsala pang'ono kumaliza! Tsopano mutha kupeza ma tebulo ndi mayina omwe ali ndi funso:
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;
GONdipo apa tikuwona kuti kudikirira pa loko kunali pa PK_Sales_OrderLines index of the Sales.OrderLines table.
Zindikirani: Mu SQL Server 2014 ndi pamwambapa, dzina lachinthu likhoza kupezekanso pogwiritsa ntchito DMO sys.dm_db_database_page_allocations yosalemba. Koma muyenera kufunsa tsamba lililonse mumsungidwe, zomwe sizikuwoneka bwino kwambiri pazosungira zazikulu, chifukwa chake ndidagwiritsa ntchito DBCC PAGE.
1.4) Kodi ndizotheka kuwona zomwe zili patsamba lomwe latsekedwa?
Nuuu, yes. Koma⦠mukutsimikiza kuti mukuzifunadi?
Zimachedwa ngakhale pamatebulo ang'onoang'ono. Koma ndizabwino, ndiye popeza mwawerenga mpaka pano...tiyeni tikambirane za %%physloc%%!
%%physloc%% ndi matsenga osalembedwa omwe amabweza chizindikiritso chakuthupi pazolowera zilizonse. mungagwiritse ntchito .
Tsopano popeza tikudziwa kuti tinkafuna kuletsa tsambalo mu Sales.OrderLines, titha kuyang'ana zonse zomwe zili patebulo ili, zomwe zasungidwa mu fayilo #3 patsamba #70133, ndi funso ili:
Use WideWorldImporters;
GO
SELECT
sys.fn_PhysLocFormatter (%%physloc%%),
*
FROM Sales.OrderLines (NOLOCK)
WHERE sys.fn_PhysLocFormatter (%%physloc%%) like '(3:70133%'
GOMonga ndidanenera, imachedwa ngakhale pamatebulo ang'onoang'ono. Ndawonjezera NOLOCK ku pempholi chifukwa sitinatsimikizirebe kuti zomwe tikufuna kuyang'ana ndizofanana ndendende ndi momwe zinalili panthawi yomwe loko idapezeka - kuti tithe kuwerenga zodetsa.
Koma, hooray, funsoli limandibwezeranso mizere 25 yomwe funso lathu lidamenyera.
Zokwanira PAGE maloko. Nanga bwanji ngati tikudikirira loko KEY?
2) waitresource="KEY: 6:72057594041991168 (ce52f92a058c)" = Database_Id, HOBT_Id (hashi yamatsenga yomwe ingathe kusindikizidwa ndi %%lockres%% ngati mukufunadi)
Ngati funso lanu liyesa kutseka pazolowera ndikudzitsekera palokha, mumapeza adilesi yosiyana kwambiri.
Kuphwanya "6:72057594041991168 (ce52f92a058c)" m'magawo, timapeza:
- database_id = 6
- hobt_id = 72057594041991168
- matsenga hashi = (ce52f92a058c)
2.1) Decrypt database_id
Zimagwira ntchito chimodzimodzi ndi chitsanzo pamwambapa! Timapeza dzina la database pogwiritsa ntchito funso:
SELECT
name
FROM sys.databases
WHERE database_id=6;
GOKwa ine, ndi chimodzimodzi .
2.2) Chotsani hobt_id
Pankhani ya nkhokwe yopezeka, muyenera kufunsa ma sys.partitions ndi zolumikizira zingapo zomwe zingakuthandizeni kudziwa mayina a tebulo ndi 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;
GOImandiuza kuti pempholi likudikirira pa Application.Maiko amatseka pogwiritsa ntchito index ya PK_Application_Countries.
2.3) Tsopano kwa ena %%lockres%% matsenga - ngati mukufuna kudziwa zomwe zidatsekedwa
Ngati ndikufunadi kudziwa mzere womwe lokoyo idafunikira, nditha kudziwa pofunsa tebulo lokha. Titha kugwiritsa ntchito %%lockres%% yosalembedwa kuti tipeze cholowa chomwe chikugwirizana ndi matsenga amatsenga.
Dziwani kuti funsoli liyang'ana tebulo lonse, ndipo pamagome akulu izi sizingakhale zosangalatsa konse:
SELECT
*
FROM Application.Countries (NOLOCK)
WHERE %%lockres%% = '(ce52f92a058c)';
GO
Ndawonjezera NOLOCK () chifukwa maloko akhoza kukhala ovuta. Timangofuna kuyang'ana zomwe zilipo tsopano, osati zomwe zinalipo pamene ntchitoyo inayamba - sindikuganiza kuti kusasinthasintha kwa deta ndikofunikira kwa ife.
Voila, mbiri yomwe tidamenyera nkhondo!
Kuyamikira ndi kuwerenga kowonjezereka
Sindikukumbukira yemwe adafotokoza zambiri mwazinthu izi, koma nazi zolemba ziwiri zazinthu zosalembedwa zomwe mungakonde:
- Zolemba za Paul Randal za (monga tili ndi deta yathu mu chitsanzo choyamba)
- Funso pa StackOverflow za (monga tapeza deta mu chitsanzo chachiwiri). Limodzi mwa mayankho limatsogolera ku positi .
Source: www.habr.com