Index Usage Statistics with ColumnList and Index Size

on April 25, 2009

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