Clear missing index DMVs

by Stackoverflowuser   Last Updated March 15, 2016 08:02 AM

How can I clear missing index DMVs such as dm_db_missing_index_group_stats, dm_db_missing_index_groups and dm_db_missing_index_details?

I don't have the option to restart the server or taking it offline first and put online back again as it is a production environment.

Answers 2

No, there is no knob for clearing just missing_index DMVs. You'll have to restart the service or, if you want to exclude specific tables, you could create a view against the DMVs with a filter.

Aaron Bertrand
Aaron Bertrand
January 05, 2015 15:42 PM

You can only clear these one table at a time (Or by restarting the server). If you rebuild an index, add an index or drop an index that table gets removed from the view.

So by extension if you create a 1-row filtered index on the primary key and then drop that index again you will remove that table. The DMV holds a max of 500 records so you would need to create at max 500 1-row indexes and drop them.

This may not be too bad but if you're running on a very heavily used system then you'll still take schema_mod locks on the table. One to create the index and one to drop the index. This can cause blocking. However I'm referring to tables that get accessed in the high hundreds to thousands of times per second. If you're not seeing that sort of access rate or you have an index rebuild window for all your tables you'll be fine to make the change at that time.

So if you happened to have a table named "test" in the "dbo" schema and a primary key on "id" then the below would clear that one table for you: -

create nonclustered index ix_dmv_drop
    on dbo.test( id )
    where id = 1
    with( online = on );
drop index ix_dmv_drop on dbo.test;

You could create a simple script from this template to drop them all. Just pull out the details from the DMV and create the create and drop command. If you link this back to something like dm_db_index_operational_stats you could get a rough access rate and therefore avoid blocking issues.

March 15, 2016 11:09 AM

Related Questions

Using DTA vs. evaluating DMVs?

Updated April 21, 2017 04:06 AM

SQL Server 2008 - Question about index behaviour

Updated June 16, 2015 12:02 PM

Clustered Index Help, where did i go wrong?

Updated August 13, 2015 18:02 PM