Explanation of SQL Server waiting for resources

Many people have encountered blocking problems. When querying, they will see the waiting resources. What do they mean?

 

Compile	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: 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 participant 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: objectid [[compile]	Tab: 5: 834102012 [[compile] This is not a table lock, but rather a compile lock on a stored procedure. database ID 5 is pubs, Object ID 834102012 is stored procedure usp_myprocedure. see Knowledge Base Article 263889 For more information on blocking caused by compile locks.

 

 

For more details, see INF: understanding and resolving SQL Server blocking problems.