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

Get query text and query plans for cached query plans

xmldeveloper — Tue, 17 Oct 2006 08:15:13 GMT

SELECT

c.[text],

a.refcounts,

a.usecounts,

d.query_plan

FROM sys.dm_exec_cached_plans a

OUTER APPLY sys.dm_exec_plan_attributes(a.plan_handle) b

OUTER APPLY sys.dm_exec_sql_text(CAST(b.VALUE AS VARBINARY)) c

OUTER APPLY sys.dm_exec_query_plan(a.plan_handle) d

WHERE b.attribute='sql_handle' AND a.cacheobjtype = 'Compiled Plan'

ORDER BY a.usecounts DESC