Tuesday 21 October 2008

Largest Index Query

Reports the Index Size (in Rows) and utilization since server startup.

select object_name(ius.object_id) + '.' + i.name, sds.name as 'FileGroup Name', si.rowcnt as 'Number of Rows',
(leaf_insert_count + leaf_delete_count + leaf_update_count) as Leaf_Change_Count,
(nonleaf_insert_count + nonleaf_delete_count + nonleaf_update_count) as Nonleaf_Change_Count,
user_seeks,
user_scans,
range_scan_count,
singleton_lookup_count,
user_lookups,
user_updates
from sys.dm_db_index_usage_stats ius
join
sys.dm_db_index_operational_stats(db_id(), NULL, NULL, NULL) ios
on
ius.object_id = ios.object_id
and
ius.index_id = ios.index_id
join sys.indexes i
on
ius.object_id = i.object_id
and
ius.index_id = i.index_id
join sys.sysindexes si
on
ius.object_id = si.id
and
ius.index_id = si.indid
join
sys.data_spaces sds
on
partition_number = sds.data_space_id
where
ius.object_id > 97
and i.name not like 'sys%'
order by si.rowcnt desc

No comments: