DMV data lifetime

I'm interested to know how long data 'resides' in a DMV - what causes that data to change etc. since they are dynamic views, and not tables, and I'm sure I've queried that, onyl to find that, later, the data is no longer in there. Is it on server restart, or is there a process or something else that defines how?

I've reviewd the BOL (http://msdn2.microsoft.com/en-us/library/ms188754.aspx) but that didn't really make me any wiser...

Cheers

Dynamic Management Views doesn't hold any data as they return server state information that can be used to monitor the health of a server instance, diagnose problems, and tune performance. The results are produced when they are called and results are dependant on underlying information existence.

|||

Thanks Satya. I was aware that these are not 'tables' that hold the data... my base question is: how long is the "underlying information" going to "exist".

For example: sys.dm_exec_query_stats ... how long will that information be in there? Since I am considering setting up some 'snapshotting jobs' that will capture some of the information available in some of the sys.dm_ views, I need to know what data is available, and how long it is available for...

|||

Geth

As per the explanation I believe it will not be stored anywhere and the results will be in cache to provide the information, even your explanation gives me another thought and would like to here from Dev. team in this regard.

|||

I'd also love to see a dev post on this topic as well, Satya. while I was hoping someone could point me to a definitive answer somewhere in BOL, it is nice to know that it isn't just a case of my not seeing/finding the answer in BOL/google/forums etc.

The closest I've come is that this data might be available until SQL Service stops - but that (a) seems very long, (b) not necessarily accurate - I am 80% sure I had some info from the query_stats DMV that was available in the morning, yet gone by the afernoon, when I wanted to show someone something... (hence the reason I am interested in capturing and persisting the interesting bits to a DB).

|||

Hello,

The answer is "it depends on the DMV".

In the case of sys.dm_exec_query_stats, the BOL says

Returns aggregate performance statistics for cached query plans. The view contains one row per query plan and the lifetime of the row is tied to the plan itself. When a plan is removed from the cache, the corresponding row is eliminated from this view.

You and other customers have expressed "I am interested in capturing and persisting the interesting bits to a DB" and we have heard you.

Thanks!