Source: Script Repository: SQL Server 2005 - List Indexes With the Most Contention
USE AdventureWorks;
GO
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
Cristian Andrei Lefter, SQL Server MVP
MCT, MCSA, MCDBA, MCAD, MCSD .NET,
MCTS, MCITP - Database Administrator SQL Server 2005
http://sqlserver.ro