Welcome to Sign in | Join | Help
in Search

List Indexes With the Most Contention

Last post 11-10-2006, 12:09 by xmldeveloper. 0 replies.
Sort Posts: Previous Next
  •  11-10-2006, 12:09 838

    List Indexes With the Most Contention

    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
    Filed under:
View as RSS news feed in XML
Powered by Community Server (Commercial Edition), by Telligent Systems