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!

Dividing the group in sections

I have a group with 3 distinct field values. I'm only interested in
seeing the specific details for one of them and lump the rest into the
"Others" category and viewing the same details but for that category as
a whole.
This is done in Crystal Reports in the Change Group Options dialog. Is
there something comparable to that in SSRS?
Thank you in advance for any assistance.I believe you can do such grouping with Reporting Services, but it may
be better to modify your data source so that you have an additional
field returned, one where the value is either the field value that you
are interested in or the word 'Other' for all other values.
In SQL Server you can do this by including a CASE statement for that
field within the overall SELECT statement.
This may run faster on the server side, as well, and it simplifies
report design.
I try to do as much as possible on the server side, so that report
design and layout are as simple as possible.
tantilis wrote:
> I have a group with 3 distinct field values. I'm only interested in
> seeing the specific details for one of them and lump the rest into the
> "Others" category and viewing the same details but for that category as
> a whole.
> This is done in Crystal Reports in the Change Group Options dialog. Is
> there something comparable to that in SSRS?
> Thank you in advance for any assistance.|||Thank you for your reply and your solution is practical however I don't
have that kind of access to the server. It's a constraint of this
project that I must replicate the Crystal report without manipulating
any stored procedures.
Parker wrote:
> I believe you can do such grouping with Reporting Services, but it may
> be better to modify your data source so that you have an additional
> field returned, one where the value is either the field value that you
> are interested in or the word 'Other' for all other values.
> In SQL Server you can do this by including a CASE statement for that
> field within the overall SELECT statement.
> This may run faster on the server side, as well, and it simplifies
> report design.
> I try to do as much as possible on the server side, so that report
> design and layout are as simple as possible.
> tantilis wrote:
> > I have a group with 3 distinct field values. I'm only interested in
> > seeing the specific details for one of them and lump the rest into the
> > "Others" category and viewing the same details but for that category as
> > a whole.
> >
> > This is done in Crystal Reports in the Change Group Options dialog. Is
> > there something comparable to that in SSRS?
> >
> > Thank you in advance for any assistance.|||i remember a report where i had to break up a group each time the last
two digits of a certain field hit either 49 or 99. the way i wound up
doing it was to add a second expression, in addition to the primary
condition, to the group expression. you might play around with this
possibility?|||I think I may have worked it out doing just that. I've changed my
primary grouping expression to look like this:
=iif(Fields!Some_Value.Value ="SomeString",Fields!Some_Value.Value,nothing)
It seems a bit counter-intuitive but in a dozen tests so far I'm seeing
values I'm expecting to see in the "Others" category.
What the report would do under this condition is first, present the
group of everything for which SomeString was true using SomeString in
the group header. The report would then present the rest of the data
in a single group while displaying only the first value of Some_Value
in alphabetic order. Just used an expression at that point to replace
every Some_Value.Value to "Others" where it wasn't SomeString.
kjward wrote:
> i remember a report where i had to break up a group each time the last
> two digits of a certain field hit either 49 or 99. the way i wound up
> doing it was to add a second expression, in addition to the primary
> condition, to the group expression. you might play around with this
> possibility?