SQL Server Performance

How to Clear Data From a Dynamic Management View

Discussion in 'SQL Server 2005 General DBA Questions' started by jbates99, Jan 11, 2011.

  1. jbates99 Member

    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
  2. FrankKalis Moderator

    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: http://sqlfool.com/2009/03/find-missing-indexes/
  3. jbates99 Member

    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.
    John

Share This Page