Sql-server – Better way of re-arranging the indexes for better performance

index-tuningperformancesql serversql-server-2012

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.

Best Answer

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.

Related Question