ααααα·αααΎα’αααααααΎαααΆαααΆαααααααΎαααΆααααααααΌαααΆαααΆααΆαα α¬αααααΌαααααΆα ααααΆααααΆααααααααααααα SQL Server ααΆαααααΆαα α’αααααΉααα½αααΏαααΌα αααα
waitresource=βαααααα 6:3:70133β
waitresource=βKEY: 6:72057594041991168 (ce52f92a058c)β
αααααααααΉαααΆαααααααΆααααααααα αααα»α XML αααααααα’ααααααα»ααα·ααααΆ (ααααΆα ααααΆααααΆααααΆααααααΈααααΆαααααα½αα’ααααααααααααααα» αα·αααααααα·αα·αααα) ααα»αααααα·ααααααααα
α’ααααααααααΉααα½αα’ααααααα»αααΆαααααααΆααα½αααα
αααααΆαααΆααα’αααααααΆααα
ααΈαααααΊαα
ααΎα’αα·αααΊαα·ααα
ααααααααααααααααΆααΆααααΆααααααααΌαααΆαα
ααα
αΆααααΆαααααΆαα! αααα»αα
ααααΆααα’αααΈααααααααΆααα½αααααΆ - ααΈ DBCC PAGE αα
hobt_id αα·ααα
αα»αααΆα %%physloc%% αα·α %%lockres%% αααααααΆαα―αααΆαα
ααΆααααΌα ααΌααα·ααΆαα’αααΈααΆααααα
αΆααα
ααΎααΆαα
αΆαααα PAGE α αΎααααααΆααααααΎαααΉααααααα
ααΆαα
αΆαααααααα
1) waitresource=βPAGE: 6:3:70133β = Database_Id: FileId: PageNumber
ααααα·αααΎαααα½αααααα’ααααααα»ααααα αΆαααΆαα αΆααααααααα SQL Server ααΉααααααα±ααα’αααααΌαα’αΆααααααΆαααααααααααα
ααΆαααααα "PAGE: 6: 3: 70133" ααΎαααα½αααΆα:
- database_id = α¦
- data_file_id = α£
- αααααααα = 70133
1.1) αα·ααααΈα database_id
α αΌαααααααααααααααΌαααααΆααα·αααααααααααααΎαααα½αα
SELECT
name
FROM sys.databases
WHERE database_id=6;
GO
αααααΆααΆααΆααα
1.2) ααααΎααααααα―αααΆααα·αααααα - ααααα·αααΎα’αααα αΆααα’αΆαααααα
ααΎαααΉαααααΎ data_file_id αααα»αααα αΆααααααΆααααΎααααΈααααααααααααααΆααΆαα α’αααα’αΆα αααααα ααα αΆααααααΆααααΆα ααα»ααααααααα·αααΎα’αααα αΆααα’αΆααααααααΎαααααα―αααΆα α’αααα’αΆα αααααααααΆααΆααααααααΎαααΆααααα½ααα αααα»αααα·ααααααΌαααααΆααα·αααααααααααΆαααααΎα ααααααα½α data_file_id αα αααα»ααααα½ααααα
USE WideWorldImporters;
GO
SELECT
name,
physical_name
FROM sys.database_files
WHERE file_id = 3;
GO
αα αααα»αααΌαααααΆααα·αααααα WideWorldImporters αααααΊααΆα―αααΆααααααΆααααααααΆ WWI_UserData α αΎααααα»αααΆαααααΆαααΆα‘αΎααα·ααα C:MSSQLDATAWideWorldImporters_UserData.ndf α (α’αΌ! α’αααα αΆαααααα»αααΆααα―αααΆααα ααΎααΆααααααααα! αα! ααΆαααα)α
1.3) αααααααααααα»ααΈ DBCC PAGE
α₯α‘αΌααααααΎαααΉαααΆααααα #70133 αα
αααα»αα―αααΆααα·αααααα 3 ααΆαααααα·αααα·ααααααΌαααααΆααα·αααααα WorldWideImporters α ααΎαα’αΆα
ααΎαααΆαα·ααΆαααααααααααααααααΎ DBCC PAGE ααααα·αααΆαα―αααΆα αα·ααααααΆα 3604α
α
αααΆαα αααα»αα
αΌαα
α·αααααααΎ DBCC PAGE αα
ααΎα
αααΆααα
αααααααααΆαααααΆαααΈααΆαααααα»ααα»ααα
ααααααααΆαα½ααα
ααΎαααΆαααΈααααααααααα αααααααΆααΆααΏαααααα·αααΆαα―αααΆαα αααα»αααααΈααααααΆα
/* 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
αααΌαααΆααααααα α’αααα’αΆα
ααααααα object_id αα·α index_idα
αα·αβαα½α
ααΆααβα αΎα! α₯α‘αΌαα’αααα’αΆα
αααααααααΆααΆα αα·αααααααα·αα·αααααααααααΎαααα½αα
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;
GO
α αΎαα₯α‘αΌααααααΎαααΎαααΆααΆααααα αΆαααααΊαα ααΎααααααααα PK_Sales_OrderLines ααααΆααΆα Sales.OrderLines α
α αααΆαα αα αααα»α SQL Server 2014 αα·ααααααααα αααααααααα»ααα’αΆα ααααΌαααΆαααααΎααααααααΎ DMO sys.dm_db_database_page_allocations ααααα·αααΆαα―αααΆαα ααα»ααααα’αααααααΌααα½ααααααααααααα αααα»αααΌαααααΆααα·αααααα αααααΎααα αα·αα‘αΌααααααΆααααΌαααααΆααα·αααααααα ααΌα αααααααα»αααΆαααααΎ DBCC PAGE α
1.4) ααΎααΆα’αΆα αα αα½α ααΎααααΈααΎααα·αααααααα ααΎααααααααααααΌαααΆαααΆααΆαααααα¬αα?
ααΆαα ααα»αααα... ααΎα’αααααααΆααααααΆα’αααααααΌαααΆαααΆ?
ααΆααΊαααΌααααΈαααα
ααΎαα»ααΌα
α ααα»ααααβααΆβα‘αΌαβααΆαα ααΌα
ααααβααΆααβααΈβα’αααβααΆαβα’αΆαβαα αΌαβαα... αααβαα·ααΆαβα’αααΈ %%physloc%%!
%%physloc%% ααΊααΆααααααααααααααααααα·αααΆαα―αααΆα ααααααα αΆαα’ααααααααΆαααΌααααααααααΆααααΆαα»ααΈαα½ααα αααβα’αααβα’αΆα
βααααΎ
α₯α‘αΌααααααΎαααΉαααΆααΎαα ααα αΆααααααααααα αααα»α Sales.OrderLines ααΎαα’αΆα ααΎααα·ααααααααΆααα’αααα αααα»αααΆααΆαααα αααααααΌαααΆααααααΆαα»ααααα»αα―αααΆααα·ααααααααα 3 αα ααΎααααα #70133 αααααααΎαααα½ααααα
Use WideWorldImporters;
GO
SELECT
sys.fn_PhysLocFormatter (%%physloc%%),
*
FROM Sales.OrderLines (NOLOCK)
WHERE sys.fn_PhysLocFormatter (%%physloc%%) like '(3:70133%'
GO
ααΌα
ααααααα»αααΆααα·ααΆα ααΆααΊαααΌααααΈαααα
ααΎαα»ααΌα
αααααα αααα»αααΆααααααα NOLOCK αα
ααΉαααααΎααα ααΈαααααααΎααα
αααα·αααΆαααΆαααΆααΆααΆαα·αααααααααααΎαα
ααααΎαααΊααΌα
ααααΆαα
ααΉαααααααααααααΌαααΆαααααΎα ααΌα
ααααααΎαα’αΆα
ααααΎααΆαα’αΆαααααααααΆαααααα»ααααα·ααΆαα
ααα»αααα αααααΆααα‘αΎα αααα½αααΆααααα‘αααααααα»ααα·αααΌα 25 αα½αααΌα
ααααΆ ααααααα½αααααααΎαααΆαααααα»ααα
αααααααααΆααα’αααΈααΆαα
αΆαααα PAGE α α
α»αβααΎβααΎαβαααα»αβαααβα
αΆαβααβαα?
2) waitresource=βKEY: 6:72057594041991168 (ce52f92a058c)β = Database_Id, HOBT_Id (ααααααα hash αααα’αΆα ααααΌαααΆααα·ααααΈααααααααΎ %%lockres%% ααααα·αααΎα’ααααα·αααΆα ααααΆα)
ααααα·αααΎαααα½αααααα’αααααααΆααΆαα
αΆαααααααααααααΆαα
αααα»ααα·αα·αααα α αΎαααααΌαααΆαα
αΆααααααααααα½αααΆ α’ααααααα
αααααααααααα’αΆααααααΆααα»αααααΆααΆαααααα»αα
ααααα β6:72057594041991168 (ce52f92a058c)β ααΆαααααα ααΎαααα½αααΆαα
- database_id = α¦
- hobt_id = 72057594041991168
- ααααααα hash = (ce52f92a058c)
2.1) αα·ααααΈα database_id
ααΆααααΎαααΆαααΌα ααααΆαα ααΉαα§ααΆα αααααΆαααΎ! ααααααααααααααΌαααααΆααα·αααααααααααααΎαααα½αα
SELECT
name
FROM sys.databases
WHERE database_id=6;
GO
αααα»αααααΈαααααααα»αααΆαα
αααααα
2.2) αα·ααααΈα hobt_id
αα αααα»αααα·ααααααΌαααααΆααα·αααααααααααΆαααααΎα α’αααααααΌαααααα·ααααα·αααα½ααα ααΆαα sys.partitions ααΆαα½αααΉαααΌαα Joins αααααΉααα½αααααααααααααΆααΆα αα·ααα·αα·αααα...
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;
GO
ααΆααααΆαααααα»αααΆααααΎαααα»ααααα αΆααα ααΎ Application.Countries lock αααααααΎααααααααα PK_Application_Countries α
2.3) α₯α‘αΌαααααααααααααααα·α %%lockres%% - ααααα·αααΎα’αααα ααααΉαααΆααΆαα»ααΆαα½αααααΌαααΆαα αΆαααα
ααααα·αααΎβαααα»αβαα·αααΆβα
ααβααΉαβααΆβααΆαβα
αΆααααβαα
βαα½αβααΆβααα αααα»αβα’αΆα
βααΉαβαααβαα½αβααΆααΆαβαααα½αβα―αα ααΎαα’αΆα
ααααΎαα»αααΆα %%lockres%% αααααααΆαα―αααΆα ααΎααααΈαααααααααΆαα»αααααααΌαααΉαααααααα hash α
ααΌαα
αααΆαααΆαααα½ααααααΉααααααααΆααΆαααΆααααΌα α αΎααα
ααΎαα»ααα ααΆαααα ααααΆαα·αααΈαααΆαααΆαααααααα
SELECT
*
FROM Application.Countries (NOLOCK)
WHERE %%lockres%% = '(ce52f92a058c)';
GO
αααα»αααΆααααααα NOLOCK (
Voila, αααααααααΆαααααΎαααΆαααααα»ααα!
ααΆαααα½αααααΆαα αα·αααΆαα’αΆααααααα
αααα»αβαα·αβα αΆαβααΆβα’αααβααΆβααΆαβαααααΆβα’αααΈβααΏαβααΆααβαααβααΆβα αααΎαβαα»αβααβαα ααα»ααααβαααβααΆβααΆαβαααααΆαβααΈαβα’αααΈβααΏαβαααβααΆαβα―αααΆαβαα·α βαααα»αβαααβα’αααβα’αΆα βα αΌαβα α·αααα
- αααααΆααααα Paul Randal
%%physloc%% αα·α sys.fn_PhysLocFormatter (ααΌα αααααΎαααΆααα·ααααααααααααΎααααα»αα§ααΆα αααααΈαα½α) - αααα½ααα
ααΎ StackOverflow α’αααΈ
ααααΎ %%lockres%% (ααΌα αααααΎαααΆαααααΎααα·αααααααααα»αα§ααΆα αααααΈααΈα)α α ααααΎααα½ααααα»αα ααααα ααααΎαααΆααα αααααΆααααααΆαGrant Fritchey α’αααΈ %%lockres%%, ααΆαααααααααα‘αααααα·ααααα»αααααΆα 2010 .
ααααα: www.habr.com