Yog tias koj siv cov txheej txheem thaiv kev tshaj tawm lossis sau cov kab kos tuag uas muab los ntawm SQL Server ib ntus, koj yuav ntsib tej yam zoo li no:
waitresource = "PAGE: 6: 3: 70133"
waitresource=βKEY: 6:72057594041991168 (ce52f92a058c)β
Qee zaum, yuav muaj cov ntaub ntawv ntau ntxiv hauv XML loj uas koj kawm (deadlock graphs muaj cov npe ntawm cov peev txheej uas pab koj nrhiav cov npe ntawm cov khoom thiab qhov ntsuas), tab sis tsis tas li.
Cov ntawv no yuav pab koj txiav txim siab lawv.
Tag nrho cov ntaub ntawv uas nyob ntawm no yog nyob rau hauv Internet nyob rau hauv ntau qhov chaw, nws yooj yim heev faib! Kuv xav muab tag nrho ua ke, los ntawm DBCC PAGE mus rau hobt_id mus rau undocumented %%physloc%% thiab %%lockres%% functions.
Ua ntej, cia peb tham txog tos ntawm PAGE locks, thiab tom qab ntawd txav mus rau KEY locks.
1) waitresource="PAGE: 6:3:70133" = Database_Id: FileId: PageNumber
Yog tias koj qhov kev thov tseem tos ntawm PAGE xauv, SQL Server yuav muab qhov chaw nyob ntawm nplooj ntawv rau koj.
Kev rhuav tshem "PAGE: 6: 3: 70133" peb tau txais:
- database_id = 6
- data_file_id = 3
- txz = 70133
1.1) Decrypt database_id
Nrhiav lub npe ntawm cov ntaub ntawv siv cov lus nug:
SELECT
name
FROM sys.databases
WHERE database_id=6;
GONws yog pej xeem ntawm kuv SQL Server.
1.2) Nrhiav lub npe ntawm cov ntaub ntawv cov ntaub ntawv - yog tias koj txaus siab
Peb yuav siv data_file_id nyob rau hauv cov kauj ruam tom ntej mus nrhiav lub rooj lub npe. Koj tuaj yeem hla mus rau kauj ruam tom ntej, tab sis yog tias koj txaus siab rau cov npe ntawm cov ntaub ntawv, koj tuaj yeem nrhiav nws los ntawm kev khiav cov lus nug hauv cov ntsiab lus ntawm cov ntaub ntawv pom, hloov data_file_id rau hauv cov lus nug no:
USE WideWorldImporters;
GO
SELECT
name,
physical_name
FROM sys.database_files
WHERE file_id = 3;
GOHauv WideWorldImporters database no yog cov ntaub ntawv hu ua WWI_UserData thiab kuv tau rov qab rau C:MSSQLDATAWideWorldImporters_UserData.ndf. (Auj, koj ntes tau kuv muab cov ntaub ntawv tso rau hauv lub system tsav! Tsis yog! Nws txaj muag).
1.3) Tau txais khoom npe los ntawm DBCC PAGE
Tam sim no peb paub tias nplooj ntawv #70133 hauv datafile 3 belongs rau WorldWideImporters database. Peb tuaj yeem saib cov ntsiab lus ntawm nplooj ntawv no siv DBCC PAGE tsis muaj ntaub ntawv thiab kab cim 3604.
Nco tseg: Kuv nyiam siv DBCC PAGE ntawm cov ntawv luam rov qab qhov chaw ntawm lwm tus neeg rau zaub mov, vim nws yog cov khoom tsis muaj ntaub ntawv. Qee zaum, nws (kwv yees. Tus neeg txhais lus - qhov txuas, hmoov tsis, ua rau tsis pom qhov twg, tab sis txiav txim los ntawm url, peb tab tom tham txog cov ntsuas ntsuas).
/* 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
Los ntawm scrolling rau cov txiaj ntsig, koj tuaj yeem pom cov khoom_id thiab index_id.
Yuav luag tag! Tam sim no koj tuaj yeem pom cov lus thiab cov npe ntsuas nrog cov lus nug:
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;
GOThiab ntawm no peb pom tias qhov tos ntawm lub xauv yog nyob rau ntawm PK_Sales_OrderLines Performance index ntawm Sales.OrderLines lub rooj.
Nco tseg: Hauv SQL Server 2014 thiab saum toj no, cov khoom npe kuj tuaj yeem pom siv cov ntaub ntawv tsis muaj ntaub ntawv DMO sys.dm_db_database_page_allocations. Tab sis koj yuav tsum nug txhua nplooj ntawv hauv database, uas tsis zoo heev rau cov ntaub ntawv loj, yog li kuv siv DBCC PAGE.
1.4) Puas tuaj yeem pom cov ntaub ntawv ntawm nplooj ntawv uas raug thaiv?
Nuuu, yes. Tab sis⦠koj puas paub tseeb tias koj xav tau nws tiag?
Nws qeeb txawm tias ntawm cov rooj me me. Tab sis nws yog qhov txias, yog li txij li koj tau nyeem tam sim no ... cia peb tham txog %% physloc%%!
%% physloc%% yog ib qho tsis muaj ntaub ntawv ntawm cov khawv koob uas xa rov qab daim ID lub cev rau txhua qhov nkag. koj siv tau .
Tam sim no peb paub tias peb xav thaiv nplooj ntawv hauv Sales.OrderLines, peb tuaj yeem saib tag nrho cov ntaub ntawv hauv cov lus no, uas tau khaws cia hauv cov ntaub ntawv #3 ntawm nplooj ntawv #70133, nrog rau cov lus nug nram qab no:
Use WideWorldImporters;
GO
SELECT
sys.fn_PhysLocFormatter (%%physloc%%),
*
FROM Sales.OrderLines (NOLOCK)
WHERE sys.fn_PhysLocFormatter (%%physloc%%) like '(3:70133%'
GORaws li kuv tau hais, nws qeeb txawm tias ntawm cov rooj me me. Kuv ntxiv NOLOCK rau qhov kev thov vim tias peb tseem tsis tau lees paub tias cov ntaub ntawv peb xav saib yog raws nraim tib yam li nws tau pom thaum lub sijhawm ntsuas xauv - yog li peb tuaj yeem nyab xeeb ua qias neeg nyeem.
Tab sis, hooray, cov lus nug rov qab rau kuv 25 kab uas peb cov lus nug tau tawm tsam.
Txaus txog PAGE xauv. Yuav ua li cas yog tias peb tab tom tos rau KEY xauv?
2) waitresource="KEY: 6:72057594041991168 (ce52f92a058c)" = Database_Id, HOBT_Id (magic hash uas tuaj yeem decrypted nrog %%lockres%% yog tias koj xav tau tiag tiag)
Yog tias koj cov lus nug sim kaw rau ntawm qhov ntsuas nkag thiab raug kaw ntawm nws tus kheej, koj tau txais qhov chaw nyob sib txawv kiag li.
Kev tawg "6: 72057594041991168 (ce52f92a058c)" rau hauv qhov chaw, peb tau txais:
- database_id = 6
- hobt_id = 72057594041991168
- khawv koob hash = (ce52f92a058c)
2.1) Decrypt database_id
Nws ua haujlwm raws nraim li qhov piv txwv saum toj no! Peb pom lub npe ntawm cov ntaub ntawv siv cov lus nug:
SELECT
name
FROM sys.databases
WHERE database_id=6;
GOHauv kuv qhov xwm txheej, nws zoo ib yam .
2.2) Decrypt hobt_id
Hauv cov ntsiab lus ntawm cov ntaub ntawv pom, koj yuav tsum nug sys.partitions nrog ob peb qhov kev koom tes uas yuav pab txiav txim siab cov npe ntawm lub rooj thiab qhov ntsuas ...
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;
GONws qhia kuv tias qhov kev thov tau tos ntawm Daim Ntawv Thov.Lub teb chaws kaw siv PK_Application_Countries index.
2.3) Tam sim no rau qee qhov %%lockres%% khawv koob - yog tias koj xav paub seb qhov twg nkag tau raug kaw
Yog tias kuv xav paub tiag tiag ntawm kab twg lub xauv qhov xav tau, kuv tuaj yeem paub los ntawm kev nug lub rooj nws tus kheej. Peb tuaj yeem siv cov ntaub ntawv tsis muaj ntaub ntawv %% lockres%% ua haujlwm txhawm rau nrhiav qhov nkag uas phim cov khawv koob hash.
Nco ntsoov tias cov lus nug no yuav luam theej tag nrho lub rooj, thiab ntawm cov rooj loj no yuav tsis lom zem kiag li:
SELECT
*
FROM Application.Countries (NOLOCK)
WHERE %%lockres%% = '(ce52f92a058c)';
GO
Kuv ntxiv NOLOCK () vim cov xauv yuav muaj teeb meem. Peb tsuas yog xav saib dab tsi muaj tam sim no, thiab tsis yog dab tsi nyob ntawd thaum qhov kev sib pauv pib - Kuv tsis xav tias cov ntaub ntawv sib xws tseem ceeb rau peb.
Voila, cov ntaub ntawv peb tawm tsam rau!
Kev lees paub thiab kev nyeem ntawv ntxiv
Kuv tsis nco qab leej twg xub piav ntau yam ntawm no, tab sis ntawm no yog ob nqe lus hais txog yam tsawg kawg nkaus uas koj yuav nyiam:
- Paul Randal's post txog (raws li peb muaj peb cov ntaub ntawv hauv thawj qhov piv txwv)
- Lus nug ntawm StackOverflow txog (raws li peb pom cov ntaub ntawv hauv qhov piv txwv thib ob). Ib lo lus teb ua rau tus ncej .
Tau qhov twg los: www.hab.com