How to Clear Data From a Dynamic Management View | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

How to Clear Data From a Dynamic Management View

Hi everyone,
I’m using (2005’s) sys.dm_db_missing_index_details DMV to identify columns that should have indexes.
It returns a lot of extraneous data that is unrelated to my query.
How can I flush the view or table just before running my query – so I will only see rows that apply to my query? Hope my question makes sense.
Thanks, John

The only way I know of to "flush" these views is to restart the SQL Server service, which isn’t really possible when we’re talking about a production server.
However, you can join this view to the others in the "missing index" group and then you can filter a variety of columns such as database_id and equality_columns and/or statement. The last two columns contain the tablename, so you can use them as another filter to narrow the result set down to your query. This is maybe a little bit tedious, but on the other hand it gives you the opportunity to think about the recommendations first. I wouldn’t blindly apply them or even automate their application without a review first anyway.
Apart from this what you could do is to restore the database a onto a nonproduction server and do the testing there or just run the query and look at the execution plan. If there is an index missing it will be shown there.
This might also be useful for you:

Thanks for the suggestion, Frank. This happens to be a test server so I can bounce MSSQL services without causing too much pain.
Please consider this thread closed.


Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |