Monday, December 16, 2013

Using sys.dm_db_index_operational_stats

In addition to sys.dm_db_index_usage_stats, I also encourage you to take a look at sys.dm_db_index_operational_stats.  This DMO (dynamic management object) can be used to return current low-level I/O, locking, latching, and access method activity for each partition of a table or index in the database.  I have used it to help me determine which indices are used, as well as HOW they are used.  By that, I mean whether you are scanning the index, or seeking, and even which tables said index is targeting.  Basically, this is one of the many tools that you can use to assess your index utilization. 

See BOL:  http://technet.microsoft.com/en-us/library/ms174281(v=sql.105).aspx

This query will return the number of inserts, updates and delete operations against all indices in the targeted database:

SELECT
    DB_NAME(database_id) [Database],
    ios.object_id [ObjectID],
    QUOTENAME(OBJECT_SCHEMA_NAME(ios.object_id,database_id)) + N'.'+     
       QUOTENAME(OBJECT_NAME(ios.object_id, database_id)) [ObjectName],
    i.index_id [IndexID],
    i.name [IndexName],
    i.fill_factor [FillFactor],
    ios.partition_number [PartitionNumber],
    CASE WHEN i.is_unique = 1 THEN 'UNIQUE ' ELSE '' END + i.[type_desc] [IndexType],
    ios.leaf_insert_count [LeafInserts],
    ios.leaf_update_count [LeafUpdates],
    ios.leaf_delete_count [LeafDeletes],
FROM
    sys.dm_db_index_operational_stats(DB_ID(),NULL,NULL,NULL) ios INNER JOIN sys.indexes i
      ON ios.object_id = i.object_id   
      AND ios.index_id = i.index_id
ORDER BY
    ios.page_latch_wait_count + ios.page_io_latch_wait_count DESC

 
This query will return the TOP 10 objects with waits on page locks:

SELECT
    TOP 10 OBJECT_NAME(os.object_id, os.database_id) [ObjectName],
    os.index_id [IndexID],
    partition_number [PartitionNumber],
        page_lock_wait_count [PageLockWaitCount],
        page_lock_wait_in_ms [PageLockWaitMS],
    CASE WHEN mid.database_id IS NULL THEN 'N' ELSE 'Y' END [MissingIndex]
FROM
    sys.dm_db_index_operational_stats(db_id(),NULL,NULL,NULL) os
LEFT OUTER JOIN
          (SELECT DISTINCT database_id, object_id
                      FROM sys.dm_db_missing_index_details id ) id
             ON os.database_id = id.database_id
             AND os.object_id = id.object_id
ORDER BY
   page_lock_wait_count DESC
 
 

No comments:

Post a Comment