A KEY or PAGE wait identifies an internal resource before you see its table. Reading wait_resource turns that identifier into a useful investigation path. KEY and PAGE resources need different mappings.
Reproduce a Small Blocking Chain
Use two SSMS windows connected to the same disposable database. Create a small demonstration table in the first window before starting the transaction. The primary key gives the row a clear index identity. Do not use a real business table to manufacture blocking for this exercise.
I record both session identifiers before running the conflicting updates. Otherwise it is easy to inspect an unrelated blocked request on a shared server. Keep the first transaction open only long enough to inspect the second window. A demonstration lock should not become a surprise afternoon task.
CREATE TABLE dbo.LockResourceDemo
(ID int NOT NULL PRIMARY KEY, Value int NOT NULL);
INSERT dbo.LockResourceDemo VALUES(1,10),(2,20);
SELECT @@SPID AS FirstSessionID;
BEGIN TRANSACTION;
UPDATE dbo.LockResourceDemo SET Value=Value+1 WHERE ID=1;
-- Leave this test transaction open while using the second window.In the second window, set a finite lock timeout and attempt the conflicting update. This bounds the wait and avoids leaving the exercise blocked indefinitely. Roll back the first transaction when inspection is complete. That releases its lock without preserving the test change.
SELECT @@SPID AS SecondSessionID;
SET LOCK_TIMEOUT 60000;
UPDATE dbo.LockResourceDemo SET Value=Value+1 WHERE ID=1;
SET LOCK_TIMEOUT -1;If timeout raises an error, run the reset statement separately afterward. Session settings persist beyond one batch. Keep the transaction cleanup in the first window independent of what the second window reports.
Read wait_resource While the Request Waits
Use a third window, or the first window before rollback, to inspect the blocked request. Filter by the known second session identifier when working on a busy server. The general query below shows current blocked requests without guessing an identifier.
SELECT session_id, blocking_session_id, wait_type,
wait_time, wait_resource, database_id, status
FROM sys.dm_exec_requests
WHERE blocking_session_id<>0;Capture the value while the request is actually waiting. After timeout or lock release, that request can disappear from the view. An empty result after the exercise finishes does not prove there was no blocking. Pair the resource with the captured time and both session identifiers.
Read wait_type too. PAGEIOLATCH and PAGELATCH concerns differ from lock blocking, even when a page identifier appears. This article maps resource names for lock investigation; it does not turn every page wait into the same diagnosis.
Map a KEY wait_resource Through Its HoBt
A traditional KEY resource contains a database identifier, a HoBt identifier, and a key hash. HoBt identifies the heap-or-B-tree structure for an index partition. Switch to the database named by the resource before querying its partitions.
SELECT DB_NAME(5) AS ExampleDatabaseName;
DECLARE @HobtID bigint=72057594045136896;
SELECT s.name AS SchemaName, o.name AS TableName,
i.name AS IndexName, p.partition_number, p.hobt_id
FROM sys.partitions p
JOIN sys.objects o ON o.object_id=p.object_id
JOIN sys.schemas s ON s.schema_id=o.schema_id
JOIN sys.indexes i ON i.object_id=p.object_id AND i.index_id=p.index_id
WHERE p.hobt_id=@HobtID;The numbers are format examples. Replace them with the identifiers captured from your blocked request. They are not expected values from the disposable table. A HoBt identifier from another database cannot be resolved by looking in the current database's catalog.
In my test, the second session waited on LCK_M_X with a resource shaped like KEY: 19:72057594047234048 (8194443284a0). Mapping that HoBt returned dbo.LockResourceDemo and its primary key index.
The key hash is not the displayed primary-key value. It identifies a lock resource representation and can collide. Mapping the HoBt gets you to the table, index, and partition first. That is the supported and useful step for understanding which relationship or access path is blocked.
Map a PAGE wait_resource With Page Information
A PAGE resource includes database, file, and page identifiers. SQL Server 2019 and later provide sys.dm_db_page_info for reading the page header. The returned object and index identifiers can then be joined to the relevant database's catalog.
DECLARE @DatabaseID int=DB_ID(), @FileID int=1, @PageID int=1;
SELECT page_id, file_id, page_type_desc, object_id, index_id
FROM sys.dm_db_page_info(@DatabaseID,@FileID,@PageID,'DETAILED');Replace file and page values with a captured PAGE resource. The sample reads page 1, which exists in every data file. It is an allocation page, so my test returned PFS_PAGE with a null object_id. A page number beyond the end of the file fails with error 2561 instead. A metadata or allocation page can have different ownership information from a data page. Preserve missing identifiers rather than assigning an unrelated table name.
For a request exposing page_resource, sys.fn_PageResCracker can extract the identifiers without manually parsing a text string. Use that supported path when available. A generic string parser should reject unknown formats rather than guess where a colon belongs.
Keep Row-Hash Checks on a Test Copy
The undocumented %%lockres%% expression can display a row's lock-resource hash. Use it only for a narrow investigation on a disposable copy. It can scan the table, its behavior is not a supported application contract, and choosing the wrong index can change the resource being examined.
SELECT ID, %%lockres%% AS LockHash
FROM dbo.LockResourceDemo WITH (INDEX(1))
ORDER BY ID;Compare the displayed hash with a captured KEY hash only after confirming the same index and partition context. Do not turn this into a production search across every table. It is a teaching aid for the sample, not a dependable reverse lookup service. In my test, row ID 1 showed the same hash as the captured KEY resource.
I stop at table and index mapping when that already explains the blocked operation. A hash lookup adds little if the application statement and transaction already identify the row. Which missing fact would the additional scan actually provide?
Account for Modern Locking Behavior
Optimized locking in SQL Server 2025 can expose transaction-related resources instead of the older resource shape expected by this exercise. Configuration and eligibility determine the observed behavior. Read the captured resource rather than forcing it into a KEY or PAGE parser.
A lock can also escalate to an object-level resource. In that case, use its object identity and inspect the transaction's scope. The absence of a KEY wait does not mean the row-level update became harmless. Resource representation and the underlying concurrency problem are related but distinct.
Finish With the Transaction Owner
Resolve the blocking cause by reviewing transaction duration, access order, indexing, and application behavior. Do not kill a session solely because its identifier appears as the blocker. Its transaction can contain important work, and rollback can itself take time.
For the exercise, roll back the first window's transaction and confirm the second window completes or reports timeout. Restore its LOCK_TIMEOUT setting. Keep the captured mapping with the relevant statement and plan. A familiar table name is the beginning of the diagnosis, not the complete repair.
Catalog mappings are snapshots too. An index rebuild can change its internal structure identifiers, and page ownership can change as storage is reused. Capture the resource and mapping close together. If the mapping no longer resolves, retain the original observation and investigate the timing. Do not keep retrying the same identifier until a different object happens to appear and then call that object the original blocker.
A captured wait_resource belongs to one request at one moment. Preserve that identity when mapping wait_resource so a later catalog change does not rewrite the original observation.
Related reading on this blog: Diagnosing Blocking Chains With sys.dm_exec_requests and Optimized Locking in SQL Server 2025: What Changes for Blocking.
wait_resource is not an unreadable error decoration, it is a typed resource address that guides a blocking investigation.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
