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
REGISTER