List Tables That May Be Over Indexed

— By Lori Brown

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 as TablesWithLargeNumInx, count( 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

having count( > 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’)

Both comments and trackbacks are currently closed.


  • Wilfred van Dijk  On April 14, 2017 at 6:33 AM

    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

%d bloggers like this: