I’ve been running this script to try to find extraneous indexes
select o.name as TableName, i.name as IndexName, p.reserved_page_count * 8.0 / 1024 as SpaceInMB, s.* from sys.dm_db_index_usage_stats s inner join sys.objects o on s.object_id = o.object_id inner join sys.indexes i on i.index_id = s.index_id and i.object_id = o.object_id inner join sys.dm_db_partition_stats p on i.index_id = p.index_id and o.object_id = p.object_id where o.name = ‘TableName’
I know that when last_user_seek/scan/lookup are all null, that no users have used the index since last restart. But I’m wondering what system_scans/lookups/seeks …are? Because on a certain table I found 5 that had no user activity, but one had system activity 10 days ago. Do anyone have any insight on what system scans/seeks/lookups might be? These tables seem really over-indexed and I’d like to trim the fat.