Mena ka whakamahi koe i te purongo tukanga kua aukatihia, ka kohia ranei nga kauwhata kati e whakaratohia ana e te SQL Server i ia wa, ka tupono koe ki nga mea penei:
waitresource="WAHI: 6:3:70133"
waitresource=“KEY: 6:72057594041991168 (ce52f92a058c)“
I etahi wa, ka nui ake nga korero kei roto i taua XML nui ka akohia e koe (kei roto i nga kauwhata aukati he rarangi rauemi hei awhina i a koe ki te rapu i nga ingoa o te ahanoa me te taurangi), engari kaore i nga wa katoa.
Ma tenei tuhinga ka awhina koe ki te whakamaarama.
Ko nga korero katoa kei konei kei runga i te Ipurangi i nga waahi rereke, he tino tohatoha noa! Kei te pirangi au ki te whakakotahi katoa, mai i te DBCC PAGE ki te hobt_id ki nga mahi %%physloc%% me te %%lockres%% kaore i tuhia.
Tuatahi, me korero tatou mo nga tatari mo nga raka PAGE, katahi ka neke ki nga raka KEY.
1) waitresource="WAHI: 6:3:70133" = Id_Patengi Raraunga: Id Kōnae: Tau Whārangi
Mena kei te tatari to tono i runga i te raka PAGE, ka hoatu e SQL Server te wahitau o taua wharangi.
Ko te wawahi i te "WAHI: 6:3:70133" ka whiwhi tatou:
- id_papatengi raraunga = 6
- raraunga_kōnae_id = 3
- nama_whārangi = 70133
1.1) Wetewetehia te raraunga_id
Kimihia te ingoa o te pātengi raraunga mā te pātai:
SELECT
name
FROM sys.databases
WHERE database_id=6;
GOHe tūmatanui i runga i taku Tūmau SQL.
1.2) E rapu ana i te ingoa o te konae raraunga - mena kei te pirangi koe
Ka whakamahia e matou te data_file_id i te taahiraa e whai ake nei ki te kimi i te ingoa ripanga. Ka taea e koe te peke ki te taahiraa e whai ake nei, engari mena kei te pirangi koe ki te ingoa konae, ka kitea e koe ma te whakahaere i tetahi patai i roto i te horopaki o te papaunga raraunga kua kitea, me te whakakapi i te data_file_id ki tenei patai:
USE WideWorldImporters;
GO
SELECT
name,
physical_name
FROM sys.database_files
WHERE file_id = 3;
GOI roto i te papanga raraunga WideWorldImporters he konae tenei e kiia nei ko WWI_UserData a kua whakahokia mai e au ki C:MSSQLDATAWideWorldImporters_UserData.ndf. (Aue, i mau koe i ahau e whakatakoto ana i nga konae ki runga i te puku punaha! Kao! He whakama).
1.3) Tikina te ingoa ahanoa mai i te DBCC PAGE
Kei te mohio matou inaianei ko te wharangi #70133 i te konae raraunga 3 no te putunga raraunga WorldWideImporters. Ka taea e tatou te titiro ki nga korero o tenei wharangi ma te whakamahi i te DBCC PAGE kore i tuhia me te haki tohu 3604.
Tuhipoka: He pai ki ahau te whakamahi i te DBCC PAGE i runga i tetahi kape kua whakahokia ki tetahi waahi kei runga i tetahi atu tūmau, na te mea he mea kore tuhinga. I etahi wa, ko ia (āhua. kaiwhakamaori - ko te hono, engari, kaore e arai ki hea, engari ma te whakatau i te url, kei te korero tatou mo nga tohu kua tohua).
/* 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 te panuku ki nga hua, ka kitea e koe te object_id me te index_id.
Kua tata oti! Inaianei ka kitea e koe te ripanga me nga ingoa taupū me te patai:
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;
GOI konei ka kite tatou ko te tatari mo te raka i runga i te taurangi PK_Sales_OrderLines o te ripanga Sales.OrderLines.
Tuhipoka: I roto i te SQL Server 2014 me runga ake, ka kitea ano te ingoa ahanoa ma te whakamahi i te DMO sys.dm_db_database_page_allocations kaore i tuhia. Engari me uiui e koe nga wharangi katoa i roto i te paataka raraunga, kaore i te tino pai te ahua mo nga papaa raraunga nui, na reira i whakamahia e ahau te DBCC PAGE.
1.4) Ka taea te kite i nga raraunga i runga i te wharangi i aukatia?
Nuu, ae. Engari... kei te tino hiahia koe?
He puhoi ahakoa i runga i nga tepu iti. Engari he ahua hauhautanga, na i te mea kua panui koe i tenei tawhiti...me korero mo %%physloc%%!
Ko %%physloc%% he mahi makutu kore tuhi e whakahoki mai ana i te ID tinana mo ia urunga. ka taea e koe te whakamahi .
Inaianei kua mohio matou kei te pirangi matou ki te aukati i te wharangi i roto i nga Sales.OrderLines, ka taea e matou te titiro ki nga raraunga katoa i tenei ripanga, kei te rongoa i te konae raraunga #3 i te wharangi #70133, me te patai e whai ake nei:
Use WideWorldImporters;
GO
SELECT
sys.fn_PhysLocFormatter (%%physloc%%),
*
FROM Sales.OrderLines (NOLOCK)
WHERE sys.fn_PhysLocFormatter (%%physloc%%) like '(3:70133%'
GOI korero ahau, he puhoi ahakoa i runga i nga tepu iti. I taapirihia e ahau a NOLOCK ki te tono na te mea karekau tonu he taurangi ko nga raraunga e hiahia ana matou ki te titiro he rite tonu ki tera i te wa i kitea ai te raka - na reira ka taea e matou te mahi panui paru.
Engari, hooray, ka whakahokia mai e te patai nga rarangi 25 tonu i whawhai ai ta matou patai.
Kati mo nga raka PAGE. He aha mena kei te tatari tatou mo te raka KUPU?
2) waitresource="KEY: 6:72057594041991168 (ce52f92a058c)" = Database_Id, HOBT_Id (hash makutu ka taea te wetemuna ki te %%lockres%% ki te tino hiahia koe)
Mena ka ngana to patai ki te maukati i tetahi urunga taurangi ka maukati i a ia ano, ka whiwhi koe i tetahi momo wahitau rereke.
Ka pakaruhia te "6:72057594041991168 (ce52f92a058c)" ki etahi waahanga, ka whiwhi:
- id_papatengi raraunga = 6
- hobt_id = 72057594041991168
- Hahi makutu = (ce52f92a058c)
2.1) Wetewetehia te raraunga_id
He rite tonu te mahi ki te tauira i runga ake nei! Ka kitea e matou te ingoa o te patengi raraunga ma te whakamahi i te patai:
SELECT
name
FROM sys.databases
WHERE database_id=6;
GOKi taku take, he rite tonu .
2.2) Wetemuna hobt_id
I roto i te horopaki o te papaarangi kua kitea, me uiui koe i te sys.partitions me nga hononga takirua ka awhina i te whakatau i nga ingoa o te ripanga me te taupū ...
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;
GOE whakaatu mai ana kei te tatari te tono i runga i te raka Application.Countries ma te whakamahi i te taupū PK_Application_Countries.
2.3) Inaianei mo etahi %%lockres%% makutu - mena kei te pirangi koe ki te mohio ko wai te urunga i maukati
Mena kei te pirangi au ki te mohio ko tehea rarangi e hiahiatia ana te raka, ka taea e au te mohio ma te patai ki te teepu ake. Ka taea e tatou te whakamahi i te mahi %%lockres%% kaore i tuhia ki te kimi i tetahi urunga e rite ana ki te hash makutu.
Kia mahara ka karapahia e tenei patai te katoa o te tepu, a, i runga i nga teepu nui, kare pea tenei e ngahau:
SELECT
*
FROM Application.Countries (NOLOCK)
WHERE %%lockres%% = '(ce52f92a058c)';
GO
I tapiritia e ahau te NOLOCK () na te mea ka raru pea nga raka. Kei te hiahia noa matou ki te titiro ki nga mea kei reira inaianei, kaua ko nga mea i reira i te wa i timata ai te tauwhitinga - Ki taku whakaaro kaore he mea nui ki a matou te riterite o nga raraunga.
Voila, te rekoata i whawhaitia e matou!
Nga mihi me etahi atu panui
Kare au e mahara ko wai i korero tuatahi i te maha o enei mea, engari e rua nga panui e pa ana ki nga mea iti rawa i tuhia e pai ana koe:
- Ko te panui a Paul Randal mo (i te mea kei a matou o maatau raraunga i te tauira tuatahi)
- He patai mo StackOverflow mo (i kitea e matou nga raraunga i te tauira tuarua). Ko tetahi o nga whakautu ka arahi ki te panui .
Source: will.com