sqlserver 如何找到 WaitResource 对应的对象名

本文链接：https://blog.csdn.net/Hehuyi_In/article/details/107936540

本文详细解析了SQL Server中常见的等待资源类型，如PAGE、KEY、OBJECT和TAB，介绍了如何通过特定的SQL查询来定位和解决数据库中的阻塞问题。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

零、常见等待类型及含义

表格内容来自 Understand and resolve blocking problems - SQL Server | Microsoft Learn

Resource	Format	Example
Table	DatabaseID:ObjectID:IndexID	TAB: 5:261575970:1 In this case, database ID 5 is the pubs sample database and object ID 261575970 is the titles table and 1 is the clustered index.
Page	DatabaseID:FileID:PageID	PAGE: 5:1:104 In this case, database ID 5 is pubs, file ID 1 is the primary data file, and page 104 is a page belonging to the titles table. To identify the object id that the page belongs to, use the DBCC PAGE (dbid, fileid, pageid, output_option) command, and look at the m_objId. For example: DBCC TRACEON (3604) DBCC PAGE (5, 1, 104, 3)
Key	DatabaseID:Hobt_id (Hash value for index key)	KEY: 5:72057594044284928 (3300a4f361aa) In this case, database ID 5 is Pubs, Hobt_ID 72057594044284928 corresponds to non clustered index_id 2 for object id 261575970 (titles table). Use the sys.partitions catalog view to associate the hobt_id to a particular index id and object id. There is no way to unhash the index key hash to a specific index key value.
Row	DatabaseID:FileID:PageID:Slot(row)	RID: 5:1:104:3 In this case, database ID 5 is pubs, file ID 1 is the primary data file, page 104 is a page belonging to the titles table, and slot 3 indicates the row's position on the page.
Compile	DatabaseID:FileID:PageID:Slot(row)	RID: 5:1:104:3 In this case, database ID 5 is pubs, file ID 1 is the primary data file, page 104 is a page belonging to the titles table, and slot 3 indicates the row's position on the page.

下面来看常见waitresource的具体分析

一、 waitresource=PAGE

waitresource=PAGE: 6:3:70133 = Database_Id : File_Id : Page_Number

database_id = 6
file_id = 3
Page_Number = 70133

有了这个信息，首先可以找到阻塞发生的DB，也可以去找文件名，不过用处不大。

SELECT name FROM sys.databases WHERE database_id=6;
SELECT name,physical_name FROM sys.database_files WHERE file_id = 3;

这里最有用的是页号，有了它就能通过DBCC定位到它是哪个表的页，是数据页还是索引页还是特殊类型的页。

DBCC TRACEON (3604);
GO
/* DBCC PAGE (DatabaseName, FileNumber, PageNumber, DumpStyle)*/
DBCC PAGE ('WideWorldImporters',3,70133,2);
GO

可以看到它是个索引，再继续找具体对象名

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;

可以查到它是Sales.OrderLines表上的PK_Sales_OrderLines索引。

能查到页中被阻塞的是哪些行吗，其实也可以。可以使用未公开的 sys.fn_PhysLocFormatter (%%physloc%%) 函数，它会返回页中对应的记录，2008及以上版本可用，但要注意即使是小表执行时间可能也很长。

SELECT sys.fn_PhysLocFormatter (%%physloc%%), * FROM Sales.OrderLines (NOLOCK) WHERE sys.fn_PhysLocFormatter (%%physloc%%) like '(3:70133%';

当然，如果生产环境的表变化非常频繁，也无法保证这些行就是之前阻塞时的行，仅作为参考。

另外还有一个方法，DBCC PAGE参数为3时，可以输出缓冲区的标题、可读形式的页头数据、每行记录的16进制数据（每行后跟分别列出的它的列值），也可以看到相应数据。

DBCC TRACEON (3604);
GO
/* DBCC PAGE (DatabaseName, FileNumber, PageNumber, DumpStyle)*/
DBCC PAGE ('WideWorldImporters',3,70133,3);
GO

二、 waitresource=KEY

KEY: 6:72057594041991168 (ce52f92a058c) = Database_Id, HOBT_Id (magic hash value)

database_id = 6
hobt_id = 72057594041991168
magic hash value = (ce52f92a058c)

DB名的找法跟前面一样，主要是hobt_id能定位到对应key名

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;

可以看到是 Application.Countries 表的 PK_Application_Countries 索引

magic hash value可以解码出%%lockres%%，用于定位被阻塞的行（缺点也跟前面类似）

SELECT * FROM Application.Countries (NOLOCK) WHERE %%lockres%% = '(ce52f92a058c)';

三、 waitresource=OBJECT

waitresource="OBJECT: 6:12347015633:1 "=Database_Id:Object_Id:PageNumber

第三个字段是PageNumber，通过它可以还原阻塞发生的现场情况，真正阻塞的临界资源是一个Page。

四、 waitresource=TAB

waitresource="TAB: 5:261575970:1"=DatabaseID:ObjectID:IndexID

在阻塞发生时，数据库应该正在做全表扫描操作，阻塞发生在一个表或索引上，查询在请求资源时把整个表都锁定了。

参考

Decoding Key and Page WaitResource for Deadlocks and Blocking - littlekendra.com

Understand and resolve blocking problems - SQL Server | Microsoft Learn

等待资源(wait_resource)解码（完整版） - 悦光阴 - 博客园

《SQL Server 2012王者归来——基础、安全、开发及性能优化》