Monitoring Index Contention With DMFs

Often when I am load testing an application with a client I want to check to see if there is any index contention in a database.  To do this, I use the DMF sys.dm_db_index_operational_stats.  For a good rundown on this DMF, I recommend reading Louis Davidson’s blog entry on sys.dm_db_index_operational_stats.  Instead of repeating that content, I’m going to show a couple scripts that I’ve used in the past for monitoring index contention.

The first script I have here lists the index contention since the metadata for each object in the database has last been reset.  The most common point for resetting the metadata for the DMF is when the SQL Server service starts.  This can also be when the index was built or when a rarely used index was brought back into the cache.

DECLARE @dbid int
 
SET @dbid = DB_ID()
 
SELECT 
    DB_NAME(database_id) AS database_name
    ,OBJECT_NAME(ios.[object_id]) AS table_name
    ,i.name AS index_name
    ,i.index_id
    ,partition_number
    ,row_lock_count
    ,row_lock_wait_count 
    ,CAST (100.0 * row_lock_wait_count / (1 + row_lock_count) AS NUMERIC(15,2)) AS pct_blocking
    ,row_lock_wait_in_ms
    ,CAST (1.0 * row_lock_wait_in_ms / (1 + row_lock_wait_count) AS NUMERIC(15,2)) AS avg_row_lock_wait_in_ms
FROM sys.dm_db_index_operational_stats (@dbid, NULL, NULL, NULL) ios
    INNER JOIN sys.indexes i ON i.object_id = ios.object_id AND i.index_id = ios.index_id
ORDER BY row_lock_wait_count DESC

The key columns that I look at when I run this query are pct_blocking and avg_row_lock_wait_in_ms.  These column provide a scope and depth to any blocking issues that are occurring.

As I mentioned above, the metadata for the DMF being used is collected from the last point that the metadata cache was refreshed.  The problem with that is sometimes when I’ve fixed the blocking issue I want to re-run the load against the database to verify that the issue has been resolved.  And I don’t want to re-start the SQL Server service to perform this test. (If there is an undocumented DBCC command that will do this, I am all ears.  I’ll even ship you bacon if that helps pry out the knowledge)

To get around this issue, I have another version of the query above that I use that grabs a snapshot of the DMF and then waits awhile before returning the final results.  To modify the length of time for the snapshot, the WAITFOR DELAY value will need to be modified.  For the script below, I am using a 5 minute delay.

DECLARE @dbid int
 
SET @dbid = DB_ID()
 
DECLARE @IndexOperationalStats table
 (
 database_id int
 ,object_id int
 ,index_id int
 ,partition_number int
 ,row_lock_count bigint
 ,row_lock_wait_count bigint
 ,row_lock_wait_in_ms bigint
 )
 
INSERT INTO @IndexOperationalStats
SELECT database_id
    ,object_id
    ,index_id
    ,partition_number
    ,row_lock_count
    ,row_lock_wait_count
    ,row_lock_wait_in_ms
FROM sys.dm_db_index_operational_stats (@dbid, NULL, NULL, NULL) s
 
WAITFOR DELAY '00:05:00.000'
 
;WITH IndexOperationalStats_Delta
AS (
SELECT s.database_id
    ,s.object_id
    ,s.index_id
    ,s.partition_number
    ,s.row_lock_count - COALESCE(t.row_lock_count, 0) as row_lock_count
    ,s.row_lock_wait_count - COALESCE(t.row_lock_wait_count, 0) as row_lock_wait_count
    ,s.row_lock_wait_in_ms - COALESCE(t.row_lock_wait_in_ms, 0) as row_lock_wait_in_ms
FROM sys.dm_db_index_operational_stats (@dbid, NULL, NULL, NULL) s
    LEFT OUTER JOIN @IndexOperationalStats t 
        ON s.database_id = t.database_id
        AND s.object_id = t.object_id 
        AND s.index_id = t.index_id
        AND s.partition_number = t.partition_number
WHERE OBJECTPROPERTY(s.OBJECT_ID,'IsUserTable') = 1
)
SELECT 
    DB_NAME(database_id) AS database_name
    ,OBJECT_NAME(ios.[object_id]) AS table_name
    ,i.name AS index_name
    ,i.index_id
    ,partition_number
    ,row_lock_count
    ,row_lock_wait_count 
    ,CAST (100.0 * row_lock_wait_count / (1 + row_lock_count) AS NUMERIC(15,2)) AS pct_blocking
    ,row_lock_wait_in_ms
    ,CAST (1.0 * row_lock_wait_in_ms / (1 + row_lock_wait_count) AS NUMERIC(15,2)) AS avg_row_lock_wait_in_ms
FROM IndexOperationalStats_Delta ios
    INNER JOIN sys.indexes i ON i.object_id = ios.object_id AND i.index_id = ios.index_id
ORDER BY row_lock_wait_count DESC
GO

Most often, I don’t use this as a tool to find blocking issues.  I’ve often already found the blocking issues through other means.  Such as having a CPU issue that tracks back to deadlocking and then finding the source of the deadlocking through deadlock graphs.

After resolving the deadlocks, I can use these queries to verify that the index contention and blocking related to the deadlock has been resolved.

I should mention that watching Law & Order SVU on Netflix really slows down writing these entries.  So if anything doesn’t make sense, feel free to blame Stabler and Bensen.