Prior to SQL Server 2005, determining index needs was a much more intense process.  The use of tracing and reviewing queries running on SQL Server would have to be performed.  In SQL Server 2005 and on, DMVs have been added which make the process more efficient and stable.  Determining index needs can be done with a query instead of a resource consuming process.  With these new abilities, another ugly practice arose out of SQL Server; applying all the indexes the DMVs said to apply.

Missing indexes can be found in detail by looking into the sys.dm_db_missing_index_details, sys.dm_db_missing_index_groups DMVs and sys.dm_db_missing_index_columns DMF.  These three objects are the base to retrieve the details to make observations on what SQL Server has determined is a missing index.  The example code below shows them in use.

SQL Server will populate these results without much (or any) consideration to the indexing that is already there.  This means that SQL Server will not take into account index problems that are referred to as overlapping or duplicate indexes.

To show this lets perform an example in the AdventureWorks database.

Using the Person.Address table in AdventureWorks, disable indexes IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode and IX_Address_StateProvinceID.

Note: To enable an index after disabling it, rebuild the index. This will enable it again

Run the following query while having “show actual execution plan” set on.

In the actual execution plan we can see SSMS 2008 R2 shows a suggestion for a missing index

With the query we wrote earlier using the DMV and DMFs, we can also see the results showing the need for an index on City

Now run the following query on the Person.Address table.

Reviewing the actual execution plan shows another index suggestion but now on City and AddressLine1.

The missing index suggestion in green shown in the actual execution plan is very helpful.  However, the second image showing the missing index DMV/DMF results is misleading.  What the query is suggesting is to create an index on City and then another index on City and AddressLine1.  This is what is referred to as an overlapping index.

Down to it with the Tip

The missing index query using the DMVs and DMF we have shown is great but as discussed, may produce results that will require analysis on the reviewers’ part.  In the example covered, the best course would be to create one index covering both query’s requirements.  (While also placing in the covering aspects of the select statement.)

The results of both queries will now perform Index Seek operations off the IDX_CITY_ADDY1_ASC index. 

Creating overlapping or duplicate indexes has a large negative impact on performance during INSERT, UPDATE and DELETE transactions.  During maintenance tasks such as fragmentation management, long runtimes due to unwanted indexes can also cause problems that should be avoided.  Optimizing any query is important.  Optimizing the use and creation of indexes themselves is just as important.

Resources

Jason Strate - Index Black Ops Series/Whitepaper and more

Index DMV Usage Considerations