SELECT DB_NAME(database_id) AS 'DB_NAME',
QUOTENAME(OBJECT_SCHEMA_NAME(ios.[object_id], database_id))
+ N'.'
+ QUOTENAME(OBJECT_NAME(ios.[object_id], database_id)) AS 'OBJECT_NAME',
i.name AS 'INDEX_NAME',
i.index_id AS 'INDEX_ID',
i.fill_factor AS'FILL_FACTOR',
partition_number AS 'PARTITION_NUMBER',
CASE WHEN is_unique = 1 THEN 'UNIQUE ' ELSE '' END + i.type_desc AS 'INDEX_TYPE',
row_lock_count AS 'ROW_LOCK_COUNT',
row_lock_wait_count AS 'ROW_LOCK_WAIT_COUNT',
CAST (100.0 * row_lock_wait_count / (row_lock_count + 1) AS NUMERIC(15,2)) AS '% BLOCKED',
row_lock_wait_in_ms AS '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 (DB_ID(), NULL, NULL, NULL) ios
INNER JOIN sys.indexes i
ON i.[object_id] = ios.[object_id]
AND i.index_id = ios.index_id
AND row_lock_wait_count > 0
WHERE OBJECTPROPERTY (ios.[object_id], 'IsUserTable') = 1
ORDER BY row_lock_wait_count DESC;You could download it here
Cheers
CU
tosc
SQL Server 2005 Books Online Scoped Search for: sys.dm_db_index_operational_stats