Index Utilization Summary

  • Comments posted to this topic are about the item Index Utilization Summary

  • Very nice.
    I needed a minor alteration.
       LEFT JOIN sys.dm_db_index_operational_stats(NULL, NULL, NULL, NULL)IOS ON Indx.object_id=IOS.object_id AND Indx.index_id=IOS.index_id AND IUS.database_id=IOS.database_id
    WHERE(Obj.type_desc='USER_TABLE' OR Obj.type_desc='VIEW')
        AND ius.database_id = DB_ID()

  • Thank you for taking the time to share this with us. I made 2 minor changes though to restrict the output to just the current database:
    1. I changed this:

    sys.dm_db_index_usage_stats IUS ON Indx.index_id=IUS.index_id AND IUS.object_id=Obj.object_id

    to this :


    .dm_db_index_usage_stats IUS ON Indx.index_id=IUS.index_id AND IUS.object_id=Obj.object_id AND IUS.database_id = DB_ID()

    2. I changed this:

    sys.dm_db_index_operational_stats (NULL,NULL,NULL,NULL )

    to this:

    sys.dm_db_index_operational_stats (DB_ID(),NULL,NULL,NULL )

    Thanks again.


  • Question: if everything (each column) from the resultset of the original index-utilization qry
    if all columns to the right of has_filter are NULLs -- does it means that the index IS NOT USED AT ALL?
    also, why database id and object id would be NULL ? table listed in col 1, index name in col 3 of the result-set -- so why db id and obj id would be nulls, really?

    THANK YOU SO MUCH in advance for possible dispersing of my doubts and confusions...


    Likes to play Chess

  • Nice,
    Also add schema, first in select
    "Schema" = object_schema_name(Obj.object_id),
    And  may be the following after the inner join sys.objects Obj on Obj.object_id=Indx.object_id line:
    and <> 'sysdiagrams'

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply