I ran a script to find overlapped and duplicate indexes for a database.

Below is the sample screen for the above said.

enter image description here

Please advise how can I arrange them or what should be the guidelines to make a better indexes before dropping them.


Some of those indexes seem to have a lot of included columns. A table definition will provide context into the width of these columns. What is the clustered index key?

You need to look at the workload being run against this table. What queries are being executed, how often are they being executed, is this going to change in the future?

I would probably start with looking at current index usage statistics to see how indexes are being used (Seeks, Scan, Key Lookups, Updates).

OBJECT_NAME([ddius].[object_id]) AS [Table name] , 
[i].[name] AS [index name] , 
FROM sys.indexes i
left outer join [sys].[dm_db_index_usage_stats] ddius on  ddius.index_id = i.index_id 
and [ddius].[object_id] = [i].[object_id] 
WHERE [ddius].[database_id] = DB_ID() 
order by [Table name]

You can also look at the missing index DMV. However, you need to take the information returned with a grain of salt. SQL will allow you to create duplicate indexes, an index creation suggestion is "recommended" for a specific query but there may be combined indexes. Unfortunately, indexing is a bit of trial and error.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.