List Indexes With the Most Contention

xmldeveloper — Fri, 10 Nov 2006 10:09:29 GMT

USE AdventureWorks;

DECLARE @dbid INT;

SET @dbid=DB_ID();

SELECT

DB_NAME(database_id) AS DbName

,OBJECT_NAME(s.[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 sys.dm_db_index_operational_stats

(@dbid, NULL, NULL, NULL) s

JOIN sys.indexes i

ON i.[object_id] = s.[object_id]

AND i.index_id = s.index_id

WHERE OBJECTPROPERTY(s.OBJECT_ID,'IsUserTable') = 1

ORDER BY row_lock_wait_count DESC