How can I clear missing index DMVs such as
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.
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.
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 ); go drop index ix_dmv_drop on dbo.test; go
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.