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
Please log in again. The login page will open in a new window. After logging in you can close it and return to this page.