Source: Script Repository: SQL Server 2005 - List Indexes With the Most Contention USE AdventureWorks;GODECLARE @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 + ...

Source: Forced and Simple Parameterization in SQL Server 2005 (TSQLNet Weblog - Cihangir Biyikoglu)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 ...