Snapshots of Index Contention

Occasionally, I need to determine where index hotspots are located within databases.  To do this, I use the DMV sys.dm_db_index_operational_stats.  I’ve talked about this DMV before while I was going through the index analysis blogs posts.

This DMV returns locking and blocking information for tables and indexes in the database.  This information is aggregated since the last time the SQL Server service was started or when the metadata cache for the index is reset.  Inactivity of an index or DDL operations are the types of a activities that can cause the metadata cache to be reset.

Sometimes though index contention may not have occurred when you are currently knee deep in performance issues.  And as such, finding the hotspot at the time of the issue is more important that knowing that the contention happened at some point since the statistics were last reset.  So just go out and restart the SQL Server service because that’s makes it easy.

To get a snapshot of the index contention without forcing the metadata cache to reset, it is necessary to baseline the current state of contention and then compare it to a future state after a period of time has past.  In the script below, the query provides a 5 minute snapshot of index contention.

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 DbName
    ,OBJECT_NAME(ios.[object_id]) AS ObjName
    ,i.name AS IndexName
    ,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 [block %]
    ,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 Waits (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