Blocking per object with sys.dm_db_index_operational_stats

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

Qui tacet, consentire videtur