While not having enough indexes can be bad for query performance, having too many indexes can also be just as bad. Use the query below to get a list of tables in your database that has more than 10 indexes.
— Tables with large number of indexes
select t.name as TablesWithLargeNumInx, count(i.name) as CountIndexes
from sys.indexes i
inner join sys.tables t
on i.object_id = t.object_id
where i.index_id > 0
group by t.name
having count(i.name) > 10
If you suspect that you have too many indexes on your tables, you can also check the sys.dm_db_index_usage_stats dynamic management view to know if indexes on your heavily indexed tables are being used well. (Hint: seeks are good and scans are not so much)
select u.user_seeks, u.user_lookups, u.user_scans
from sys.dm_db_index_usage_stats u
inner join sys.indexes i
on u.object_id = i.object_id and u.index_id = i.index_id
WHERE u.object_id=object_id(‘dbo.SomeTableName’)
Session expired
Please log in again. The login page will open in a new tab. After logging in you can close it and return to this page.
Keep in mind this query is selecting every index, also the indexes being used for primary keys or constraints (don’t touch these!). I also recommend adding the column u.user_updates to compare the ratio between read and write. Final note: make sure the instance is running for several days (weeks?) before you make a decision about an index