I ran a script to find overlapped and duplicate indexes for a database.
Below is the sample screen for the above said.
Please advise how can I arrange them or what should be the guidelines to make a better indexes before dropping them.
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
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).
SELECT OBJECT_NAME([ddius].[object_id]) AS [Table name] , [i].[name] AS [index name] , i.type_desc, user_seeks, user_scans, user_lookups, user_updates 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.