Missing index suggestion – Duplicate Index

The missing index feature, while tuning or checking estimated and actual plans, is very helpful.  Something that is key in that sentence is the word, helpful.  Don’t run out and simply create all of them.  Make sure it is a viable solution that does not implement the three major faults in index strategies: duplicates, overlapping and misalignment.

In SQL Server 2008 and previous versions, the missing index suggestions had a little feature (read as bug) that would suggest an identical index to what was already on the table.  In fact, in some cases, it would use the index that was a duplicate and still suggest to create it.  You can see a great article on this from Paul Randal, “Missing index DMV bug that could cost your sanity…“.

With SQL Server 2012, this was thought to be resolved but we still can end up with the same situation.

For example, the table the example below is based on is a 290 million row table, partitioned by date.  The date value is a range from the first day in a month, to the last.  There is also a record ID for other purposes that is set with IDENTITY(1,1).  When querying by this record ID, the plan suggests creating an index that is identical to the one being used in the plan. Partitioning, the query and the table structure is not what we will talk about today.  What we will talk about is the reaction SQL Server 2012 took and suggested to make this query more effective.  Remember – suggestions are, suggestions.

Query

select max(recordid) from myTable where recordid > 1

Plan

The missing index details

CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[myTable] ([recordid])
GO

The index, IDX_RECORDID_ASC definition

CREATE NONCLUSTERED INDEX [IDX_RECORDID_ASC] ON [dbo].[myTable]
(
	[recordid] ASC
)
GO

As we can see, the two indexes are duplicated.  If you were to create this suggested index, you would have a major performance impact due to the size of this table.

Summary

We do not always see performance issues from duplicated indexes from blindly creating indexes suggested by SQL Server.  This is typically due to the fact that tables may not be large enough at the time to really see how the impact can degrade performance.  When indexing, you always want to think, at a minimum, 20 times larger than what the table is and how it may impact how the table is used.  If you have that mindset, even if it does not always apply to table like metadata tables, you’ll implement better indexing and better management solutions.