LCK_M waits tell you sessions are blocked, but they do not name the index where work piles up. The indexes behind lock waits become visible when you read operational counters by object and index. Use that ranking to find a hot path, then connect it to the actual queries.
Server Waits Do Not Name the Indexes Behind Lock Waits
Server-wide wait statistics show how much time sessions spent waiting on lock types. They are useful for recognizing a blocking problem, but they do not map a wait to one table or index. sys.dm_db_index_operational_stats tracks row and page lock waits for each index or heap partition. It also tracks page latch waits, which are a different kind of contention.
I start with a time window when users reported blocking. A lifetime counter can rank an index that was busy last month above the one causing today's problem. Capture two snapshots and compare deltas. What table and query were active while LCK_M rose? The index counters help narrow the search, not replace the blocking chain.
Rank the Indexes Behind Lock Waits
The function accepts database, object, index, and partition IDs. NULL values mean all within the database scope you pass. Join the returned IDs to names and sum partitions for a first ranking. Keep the two lock wait types separate so a page-level hot spot does not disappear in one total. The query below ranks the indexes behind lock waits in the current database.
SELECT OBJECT_SCHEMA_NAME(s.object_id) AS schema_name,
OBJECT_NAME(s.object_id) AS table_name,
i.name AS index_name,
SUM(s.row_lock_wait_in_ms) AS row_lock_ms,
SUM(s.page_lock_wait_in_ms) AS page_lock_ms,
SUM(s.row_lock_wait_count) AS row_waits,
SUM(s.page_lock_wait_count) AS page_waits,
SUM(s.index_lock_promotion_attempt_count)
AS escalation_attempts,
SUM(s.index_lock_promotion_count)
AS escalations
FROM sys.dm_db_index_operational_stats
(DB_ID(), NULL, NULL, NULL) AS s
JOIN sys.indexes AS i
ON i.object_id = s.object_id AND i.index_id = s.index_id
WHERE OBJECTPROPERTY(s.object_id, N'IsUserTable') = 1
GROUP BY s.object_id, i.name
ORDER BY SUM(s.row_lock_wait_in_ms)
+ SUM(s.page_lock_wait_in_ms) DESC;A heap can have a NULL index name, so include the table name when reading the result. The counts are cumulative while their metadata cache entry survives and can reset independently. Save a snapshot with a timestamp and repeat during the incident. Do not subtract counters blindly if an object was rebuilt or its cache entry reset.
Keep Latches in a Separate Column
Page latch waits are not row or page lock waits. They describe contention for in-memory page structures. A page I/O latch indicates waiting for a page to be read from storage. Mixing these numbers can send you toward row versioning when the real issue is a last-page insert hot spot or slow I/O. The same function exposes both, so put them beside the lock figures without adding them together.
SELECT OBJECT_SCHEMA_NAME(s.object_id) AS schema_name,
OBJECT_NAME(s.object_id) AS table_name,
i.name AS index_name, s.partition_number,
s.page_latch_wait_in_ms,
s.page_io_latch_wait_in_ms,
s.row_lock_wait_in_ms,
s.page_lock_wait_in_ms
FROM sys.dm_db_index_operational_stats
(DB_ID(), NULL, NULL, NULL) AS s
JOIN sys.indexes AS i
ON i.object_id = s.object_id AND i.index_id = s.index_id
WHERE s.page_latch_wait_in_ms > 0
OR s.row_lock_wait_in_ms > 0
OR s.page_lock_wait_in_ms > 0
ORDER BY s.page_latch_wait_in_ms DESC;A large latch counter can coexist with a lock problem, but the fixes differ. Read the wait names from current sessions and inspect the plan before choosing an intervention. I keep a separate line in my notes for locks, memory latches, and I/O latches. One big number labeled contention is not a diagnosis.
Find the Query Holding the Line
During a live incident, join sys.dm_exec_requests to sys.dm_exec_sessions and look at blocking_session_id, wait_type, and query text. The blocker can be sleeping with an open transaction and therefore absent from the active-request DMV. In that case, inspect session and transaction DMVs or a captured activity snapshot. Operational stats tell you where waits accumulate; they do not identify the session that owns a lock.
SELECT r.session_id, r.blocking_session_id,
r.wait_type, r.wait_time,
r.database_id, s.program_name,
t.text AS batch_text
FROM sys.dm_exec_requests AS r
JOIN sys.dm_exec_sessions AS s
ON s.session_id = r.session_id
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
WHERE r.blocking_session_id <> 0;Look at the blocked query's plan and the blocker's transaction. A broad scan can acquire or hold more locks than a selective seek. A long transaction can hold a modest number of locks for too long. Both cases can elevate the same LCK_M family of waits. Fix the behavior you observed.
Test a Narrower Access Path
If a hot read scans many rows to find a few, an index aligned with its predicate can reduce pages visited and locks taken. Key order matters: equality keys followed by a useful range can turn a broad read into a narrow one. Check whether an existing index can be extended rather than adding a duplicate. Measure logical reads, lock waits, and write cost before and after.
For a writer, an extra index adds maintenance and can increase lock work. A narrow index that helps one read can be a net loss on an update-heavy table. I test representative peak concurrency, not only one isolated query. A lock problem is about overlap between sessions, so a single fast execution is weak evidence.
Consider Row Versioning for Readers
READ_COMMITTED_SNAPSHOT lets ordinary read-committed readers use row versions instead of waiting behind writers. It can reduce reader-writer blocking, but it changes read semantics and increases version-store work. Check the current setting, application assumptions, and tempdb or persistent version-store capacity before changing it. It does not remove writer-writer conflicts or fix a poor index.
SELECT name, is_read_committed_snapshot_on,
snapshot_isolation_state_desc
FROM sys.databases
WHERE database_id = DB_ID();If the problem is a reader held behind writes, versioning can be a strong option after a workload test. If the problem is two writers updating the same rows, different transaction boundaries or application logic are needed. A setting cannot make conflicting writes independent.
Recheck the Indexes Behind Lock Waits After a Change
After a change, compare the same index counters over comparable time windows and check server LCK_M deltas, blocked-session counts, and user latency. Index counters can reset, so note restart and rebuild events. Keep the query plan and workload volume beside the before and after numbers. A lower wait total during a quiet hour is not proof that the index fixed peak blocking.
I close the investigation with the table, index, blocker pattern, change, and measured effect. The DMV ranking is the doorway to the problem. The final answer comes from matching that doorway to a query and a transaction that users actually ran.
The operational counters are cumulative for a cached metadata object. A snapshot table therefore needs the object ID, index ID, partition number, collection time, and the counters being compared. If the second number is lower, mark that interval as reset instead of reporting a negative wait. On a partitioned table, inspect per-partition figures after the first ranking; one busy partition can dominate the whole object.
Related reading on this blog: Locking, Blocking, and Deadlocking: Differences, Similarities, and Best Practices and Blocking Tree: Identifying Blocking Chain Using SQL Scripts.
An index lock counter is not the root cause, it is a pointer to a query and transaction.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
