locked
Flushing the missing index DMVs' RRS feed

  • Question

  • Hi,

     

    Is there a method available that can flush the contents of the various missing index DMV's (sys.dm_db_missing_index_*)?

     

    From researching on the net there would appear not to be.

     

    My reasoning for wanting to do this is that when releasing an updated version of source code into a production database, I would like to reset the missing indexes DMV's in order to provide a more representative view of performance from the release point forward. It is not practical to have to restart an instance in order to flush the tables, in my opinion.

     

    I would be interested to know your thoughts.

     

    Many Thanks,

    Wednesday, July 23, 2008 8:36 AM

Answers

  • John,

     

    One suggestion:

     

    1. Make up DMV snapshot table with needed columns + a new timestamp column, populate it

    2. Execute delta  (DMV - SnapshotOfDMV) scripts agains the DMV's and snapshots

     

    In fact you can use the snapshot table any time you are ready to take new readings.

     

    Let us know if helpful.

     

    Wednesday, July 23, 2008 11:30 AM

All replies

  • John,

     

    One suggestion:

     

    1. Make up DMV snapshot table with needed columns + a new timestamp column, populate it

    2. Execute delta  (DMV - SnapshotOfDMV) scripts agains the DMV's and snapshots

     

    In fact you can use the snapshot table any time you are ready to take new readings.

     

    Let us know if helpful.

     

    Wednesday, July 23, 2008 11:30 AM
  • Hi,

     

    Thanks for your reply.

     

    A good solution, although will only work on the Enterprise Edtion.

     

    That said, I am sure I could put together a physical table implementation using a similar concept.

     

    Again, thanks for your input!

     

     

     

    Wednesday, July 23, 2008 11:54 AM
  • John,

     

    Clarification. Actually I meant (newly created) physical tables acting in the capacity of DMV "snapshots'.

     

    Wednesday, July 23, 2008 12:30 PM
  • Thanks for the clarification. Great mind think alike

    Wednesday, July 23, 2008 12:32 PM