Index Usage Statistics with ColumnList and Index Size

As an add on to my last post, here is what I currently do use to track index usage. This shows usage, columns in the index, and index size on disk. The size can be quite useful to know when evaluating how much an index is worth– typically if this index is large then you’re paying a fair amount on the inserts.

If it’s not easy to tell the data types from your column names, that is a modification you’d definitely want to make. Remember that indexes that have a uniqueidentifier at the head are much more likely to cause page splits and be more work to maintain, so those indexes are more “expensive”. (In my current system I do have the luxury of a consistent naming convention where it’s fairly easy to tell the datatypes in indexed columns, so I haven’t added the datatype to the column list.)

The data represents usage since last sql server restart. I am about to start working on a project to automate collection and storage for review over a longer period of time. It’s been on the list for a while and is finally just about in the top priority spot.

select objName = object_name(i.object_id) , indexName = i.name , i.index_id , i.type_desc , c.indexColumns , d.user_updates , d.user_seeks , d.user_scans , d.user_lookups , d.system_updates , d.system_seeks , d.system_scans , d.system_lookups , IndexSizeGB = ( select cast(sum(au.Total_Pages) * 8 / 1024./1024. as decimal(10,2)) from sys.partitions par (nolock) left join sys.allocation_units au (nolock) on par.partition_id=au.container_id left join sys.data_spaces ds with (nolock) on i.data_space_id = ds.data_space_id where i.[object_id]= par.[object_id] and i.index_id=par.index_id ) from sys.indexes i join sys.dm_db_index_usage_stats d on d.object_id=i.object_id and i.index_id=d.index_id and d.database_id = db_id() left join (select distinct object_id , index_id , indexColumns = (SELECT col_name(object_id,column_id ) as ‘data()’ FROM sys.index_columns t2 where t1.object_id =t2.object_id and t1.index_id = t2.index_id FOR XML PATH (”) ) FROM sys.index_columns t1 ) c on c.index_id = i.index_id and c.object_id = i.object_id where objectproperty(i.object_id, ‘IsIndexable’) = 1

select
    objName = object_name(i.object_id)
    , indexName = i.name
    , i.index_id
    , i.type_desc
    , c.indexColumns
    , d.user_updates
    , d.user_seeks
    , d.user_scans
    , d.user_lookups
    , d.system_updates
    , d.system_seeks
    , d.system_scans
    , d.system_lookups
    , IndexSizeGB = (
            select
            cast(sum(au.Total_Pages) * 8 / 1024./1024. as decimal(10,2))
            from sys.partitions par (nolock)
            left join sys.allocation_units au (nolock) on
                par.partition_id=au.container_id
            left join sys.data_spaces ds with (nolock) on
                i.data_space_id = ds.data_space_id
            where
                i.[object_id]= par.[object_id]
                and i.index_id=par.index_id
            )

from sys.indexes i
join sys.dm_db_index_usage_stats d on
    d.object_id=i.object_id
    and i.index_id=d.index_id
    and d.database_id = db_id()
left join
    (select distinct
        object_id
        , index_id
        , indexColumns =
            (SELECT col_name(object_id,column_id ) as 'data()'
            FROM sys.index_columns t2
            where t1.object_id =t2.object_id
            and t1.index_id = t2.index_id FOR XML PATH ('')
            )
        FROM  sys.index_columns t1
    ) c on
    c.index_id = i.index_id
    and c.object_id = i.object_id
where
    objectproperty(i.object_id, 'IsIndexable') = 1
Previous Post
Everything About Your Indexes (well, almost)
Next Post
The DBA Sees Your Blocking Spids… A Bird’s Eye Summary of Blocking

Related Posts

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Menu