DMVs go how far back?

Question

Post reply

DMVs go how far back?

Sioban Krzywicki

One Orange Chip

Points: 27770
More actions
January 24, 2012 at 1:43 pm

#254227

I've been trying to find all the queries that affect a particular table that have been run recently. There are DMVs that will find this information for me and everything I've read/heard about DMVs say they give you all the information since the last time SQL Server was started.
This instance was last started on 1/3/2012. The oldest information that comes up in the DMV is from today. I know I ran queries against that table yesterday.
Anyone have any idea what's going on? Do backups wipe DMV information? Does anything other than a restart? Is there a limit to how much data is stored, like in the SQL Server Agent history?
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

it depends on WHICH DMV also;

sys.dm_exec_connections are only valid while the spid is connected, i think;

sys.dm_db_index_usage_stats is good until a stop start,

but i think sys.dm_exec_query_plan would get cleared if the procedure cache got cleared, which would include a restore or dbcc freeproccache, right?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Sioban Krzywicki One Orange Chip Points: 27770 More actions · Answer 2

I should have mentioned which DMVs I'm using, shouldn't I? 🙂

sys.dm_exec_query_stats

sys.dm_exec_sql_text

sys.dm_sql_referenced_entities

--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams

Lowell SSC Guru Points: 323518 More actions · Answer 3

my examples of that would depeend on how much memory you had for plans to stay cached in memory, i think;

SELECT

fn.*,

st.*

FROM sys.dm_exec_query_stats st

CROSS apply sys.DM_EXEC_SQL_TEXT(st.[sql_handle]) fn

WHERE st.max_elapsed_time >= ( 1000 * 15 ) --15 seconds

plans can get aged out due to non use, but will stay longer if you had boatloads of memory; i think free proccache or a restore would also clear stats for any database that got restored/cleared, though.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Stefan Krzywicki (1/24/2012)
I should have mentioned which DMVs I'm using, shouldn't I? 🙂
sys.dm_exec_query_stats
sys.dm_exec_sql_text

Those two keep data as long as the plan is in cache. Once the plan is removed (aged out, invalidated, plan cache partially or completely cleared, etc), the data is no longer available from those DMVs

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Sioban Krzywicki One Orange Chip Points: 27770 More actions · Answer 5

Awesome. I'm sure that's what's happening. Thanks for the help.

--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams