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