sqlskills-logo-2015-white.png

Clearing “missing index” suggestions for a single table

Today there isn’t a fine-tuned way to clear the accumulated statistics from the “missing index” set of DMVs.  Certainly restarting SQL Server will do this, but you don’t have something like DBCC SQLPERF (‘sys.dm_os_wait_stats’, CLEAR) to reset statistics for dm_db_missing_index_columns, dm_db_missing_index_groups, dm_db_missing_index_group_stats and dm_db_missing_index_details.

While we don’t have this direct option, there is an interesting behavior that I’ll demonstrate on this blog post using SQL Server 2008 R2 (10.50.1617).

Right after restarting SQL Server, I run the following query to generate a few entries in the missing index DMVs (each query spawns a missing index recommendation):

USE AdventureWorks

GO

 

SELECT ProductID

FROM Sales.SalesOrderDetail

WHERE UnitPriceDiscount = 0.40

GO

 

SELECT SalesOrderID

FROM Sales.SalesOrderDetail

WHERE LineTotal = 236.421500

GO

 

SELECT SalesOrderID

FROM Sales.SalesOrderHeader

WHERE TaxAmt = '10.316'

GO

Next I execute the following query to see the three entries in the missing index DMVs:

SELECT      s.last_user_seek,

            d.object_id,

            d.equality_columns,

            d.inequality_columns,

            d.included_columns,

            d.statement,

            s.avg_user_impact

FROM sys.dm_db_missing_index_group_stats AS s

INNER JOIN sys.dm_db_missing_index_groups AS g

    ON (s.group_handle = g.index_group_handle)

INNER JOIN sys.dm_db_missing_index_details AS d

    ON (g.index_handle = d.index_handle)

This returns:

clip_image002

So I have three index suggestions so far. What happens if I create one of suggested indexes for the SalesOrderDetail table?

USE [AdventureWorks]

GO

 

CREATE NONCLUSTERED INDEX IDX_SalesOrderDetail_UnitPriceDiscount

ON [Sales].[SalesOrderDetail] ([UnitPriceDiscount])

INCLUDE ([ProductID])

GO

If I re-execute the query against the missing index DMVs – I’ll now see that BOTH suggestions from the SalesOrderDetail table got cleared out (even though I only added one of the suggestions), leaving behind the suggestion for the other table (SalesOrderHeader):

clip_image004

So by virtue of adding an index to SalesOrderDetail, it clears out BOTH suggestions for that table. 

And if I re-execute the query for the LineTotal column on SalesOrderDetail (that was not yet indexed), the missing index entry pops back in (since we didn’t create an index for this):

clip_image006

And just to test from another direction, I'll add the suggested index on the SalesOrderHeader table:

USE [AdventureWorks]

GO

 

CREATE NONCLUSTERED INDEX IDX_SalesOrderHeader_TaxAmt

ON [Sales].[SalesOrderHeader] ([TaxAmt])

Re-running the query against the missing index DMVs, sure enough, the SalesOrderHeader suggestion is removed, leaving the SalesOrderDetail suggestion (for the one index we haven’t yet created):

clip_image008

So in a nutshell, the missing index views aren’t as static as they may appear to be and if you’re adding indexes, you may expect to see suggestions disappear for that table (not waiting for a SQL Server restart) – and this happens whether or not you created indexes for each suggestion for a specific table.  I can understand the trade-offs of this behavior – especially since a new index on a table can have an impact on future execution plans.  Basically one index creation “resets” the suggestions.  But that also means that you should be sampling these DMVs over time and not rely fully on a  single sampling.

3 thoughts on “Clearing “missing index” suggestions for a single table

  1. Nice post, Joe. Creating a purposely small filtered index on a large table and then dropping it is a good shortcut to get this without too much effort for the database server.

Comments are closed.

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.