Rank Index Contention with sys.dm_db_index_operational_stats

calendar Oct 22, 2026 / · 7 min read · sql server sys.dm_db_index_operational_stats sys.indexes sys.dm_os_wait_stats sys.dm_db_index_usage_stats lock contention row locks blocking indexes performance tuning dmv tsql  ·
Share on: linkedin copy

Once sys.dm_os_wait_stats points to locking, the next question is which index the waits belong to. The script below reads sys.dm_db_index_operational_stats for the current database and ranks each user-table index by its number of row-lock waits. Next to each count it returns the share of row-lock requests that had to wait and the average wait in milliseconds.

Purpose and Overview

sys.dm_db_index_operational_stats returns lower-level data access, locking and latching statistics for each partition of a table or index. Because it reports per index, it can tell you which index the row-lock waits belong to.

The script returns one row for each index partition that has at least one row-lock wait, sorted by wait count, highest first. row_lock_wait_in_ms shows whether the index or heap has lock contention, and how significant it is.

The function and the row_lock_count, row_lock_wait_count and row_lock_wait_in_ms columns all appear in the SQL Server 2005 documentation, so SQL Server 2005 is the minimum version, with the database at compatibility level 90 (see Pinning the database id below). The current reference page also covers Azure SQL Database, Azure SQL Managed Instance and SQL database in Microsoft Fabric. The function returns nothing for indexes on memory-optimized tables.

Code Breakdown

Run the full script in the database you want to inspect:

 1declare @dbid int
 2select @dbid = db_id()
 3
 4Select dbid=database_id
 5	, objectname=object_name(s.object_id)
 6	, indexname=i.name, i.index_id	--, partition_number
 7	, row_lock_count, row_lock_wait_count
 8	, [block %]=cast (100.0 * row_lock_wait_count / (1 + row_lock_count) as numeric(15,2))
 9	, row_lock_wait_in_ms
10	, [avg row lock waits in ms]=cast (1.0 * row_lock_wait_in_ms / (1 + row_lock_wait_count) as numeric(15,2))
11from sys.dm_db_index_operational_stats (@dbid, NULL, NULL, NULL) s
12	inner join sys.indexes i
13		on i.index_id = s.index_id
14		and i.object_id = s.object_id
15		
16where objectproperty(s.object_id,'IsUserTable') = 1
17and i.object_id = s.object_id
18and row_lock_wait_count > 0
19order by row_lock_wait_count desc

Pinning the database id

db_id() with no argument returns the id of the current database. The script stores that id in @dbid and passes the variable to the function, so the scan stays inside the database you are connected to. On SQL Server 2005, calling DB_ID without a database name required compatibility level 90 in the current database, and the script's call has no name.

The variable matters if you change the script to take a database name. DB_ID('SalesDb') returns NULL when the database does not exist or the name is misspelled, and the function treats NULL as a wildcard for all databases. You get a scan of every database on the instance instead of an error. If you make that change, add an IS NOT NULL check on @dbid before the SELECT.

The function call and the sys.indexes join

The call sys.dm_db_index_operational_stats(@dbid, NULL, NULL, NULL) passes NULL for the object, index and partition arguments. NULL in those three positions returns all tables and views, all indexes, and all partitions of each index or heap. The statistics come at the partition level and are additive.

The join to sys.indexes on object_id and index_id adds the index name. Read index_id next to that name: 0 is a heap, 1 is the clustered index, and anything above 1 is a nonclustered index. For a heap, sys.indexes.name is NULL, so a heap with row-lock waits shows up with a blank indexname and index_id 0.

The WHERE clause repeats i.object_id = s.object_id, which the join already applies. The repeat has no effect on the result.

Filtering to user tables

objectproperty(s.object_id,'IsUserTable') = 1 keeps only rows whose object is a user table. The function returns rows for views as well as tables, and this filter removes them. row_lock_wait_count > 0 then drops every index that never waited on a row lock. The ORDER BY puts the index with the most row-lock waits first.

The two ratios

[block %] divides row-lock waits by row locks requested and multiplies by 100. It is the percentage of row-lock requests on that index that had to wait. [avg row lock waits in ms] divides total wait time by the number of waits.

The 100.0 and 1.0 literals are deliberate. If a formula has no decimal, float or other non-integer type, the result is an integer. Without the literals the fraction would be truncated: the block percentage would only show whole numbers, so any rate under 1% would read as 0, and the average wait would lose its decimals.

The 1 + in each denominator prevents a divide-by-zero error, but it skews small counts. An index with 2 row locks and 2 waits shows a block percentage of 66.67 instead of 100. One 500 ms wait shows an average of 250.00. On an index with millions of row locks the offset disappears. On a quiet index, read the raw row_lock_count and row_lock_wait_count columns before you trust either ratio.

What the ranking leaves out

partition_number is commented out of the select list. Because the function returns one row per partition, a partitioned index appears as several rows with the same name and index_id and nothing to tell them apart. A nonpartitioned index or heap has partition_number set to 1, so it is unaffected. Removing the -- before , partition_number labels each row. Because the counters are additive, you can also sum them per index.

The script reads only row locks. The function reports page locks the same way, with page_lock_count, page_lock_wait_count and page_lock_wait_in_ms next to the three row-lock columns, so page-lock contention is missing from this output. Latch contention (page_latch_wait_count, page_latch_wait_in_ms) and lock escalation (index_lock_promotion_count) are not read either. Add those columns to the select list to cover them.

Key Benefits and Use Cases

  • No setup objects — the script declares one variable and runs one SELECT, so you can paste it into any database without creating tables or procedures first.
  • Two rankings from one query — sort by row_lock_wait_count to find frequent short waits, or change the ORDER BY to row_lock_wait_in_ms to find fewer, longer waits.
  • Reads counts that already exist — the function returns what SQL Server has accumulated since the index's metadata entered the cache, so the first run already shows past waits.
  • The function needs only database-level permission — VIEW DATABASE STATE or VIEW DATABASE PERFORMANCE STATE on the database covers every object in the function when no object id is passed. The sys.indexes join is a separate limit (see Catalog visibility below).

Performance Considerations

  • Counter lifetime: each counter is set to zero when the heap or B-tree metadata enters the metadata cache. An active index usually keeps its metadata in the cache, so its counts usually cover activity since the instance last started. Metadata for a less active heap or B-tree can move in and out of the cache, particularly under memory pressure, so a low count on a quiet index is not a full history.
  • Some DDL resets the counters: dropping a table or index, or truncating a partition, removes its rows from the output, and other DDL against the index might reset them to zero.
  • Memory-optimized tables: indexes on memory-optimized tables are not returned, so In-Memory OLTP tables never show up in the ranking.
  • Catalog visibility: sys.indexes shows only securables the caller owns or has been granted permission on. The inner join silently drops rows for tables the caller cannot see, with no error.

Practical Tips

  • Save snapshots and diff them — persist readings to a logging table on a schedule and subtract one reading from the next to get waits per interval. For several databases, loop and pass each id, because the function doesn't accept correlated parameters from CROSS APPLY and OUTER APPLY.
  • Read escalation against the waits — escalation lowers overhead but reduces concurrency, so when you add index_lock_promotion_count, compare a high value against the same index's wait counts.
  • Use sys.dm_db_index_usage_stats for usage questions — these counters can't conclusively show whether an index was used, or when.
  • Narrow it to one table — pass the table's id as the second argument, for example sys.dm_db_index_operational_stats(@dbid, OBJECT_ID('dbo.Orders'), NULL, NULL), to return only that table's indexes when you already know where the blocking is.

Conclusion

The script ranks user-table indexes by row-lock waits, with the block rate and average wait next to each one, so you know which index to look at before you rewrite queries or change indexes. Snapshots and the page-lock and latch columns cover what this version leaves out.

References

Posts in this series