November 7, 2008 at 4:59 am
Hi,
I'm currently writing some SQL Server 2005 scripts using the built in DMVs to gather information on the state of a SQL Server 2005 Enterprise Edition Instance running on Win2K3 Enterprise Edition.
I've searched the net but have been unable to find answers to the following questions:
1. I'm accessing quite a few DMVs and I'm wanting to know how up to date the data returned from the DMVs is?
2. How long the data in a DMV is retained for?
3. Do some DMVs contain realtime information, and some contain data that is collected every 5 or 10 or 15 etc... minutes?
I'm using the DMVs to extract certain information and store it in a serarate database so we have some historical information. My scripts that access the DMVs will be executed via SQL Server scheduled jobs, and I don't want to set a job execution frequency of every minute (for example) if the data returned from a DMV is only updated every 5 minutes.
Any help would be much appreciated as I've been trying to find this information out for quite a while now.
Many thanks in advance.
www.sqlAssociates.co.uk
November 7, 2008 at 6:29 am
Hi Chris,
The DMV's collect real-time information. So, it makes sense for some of them to schedule a SSA job every 5 minutes to capture the necessary information.
Franky L.
November 7, 2008 at 6:34 am
Hi Chris,
Any statistic is continously updated but it can depend on how busy your server is. In 2000 it sometime happened that various stats were not update but in 2005 is should all be updated in a timely manner.
A DMV such as sys.dm_exec_query_stats will be updated when a query finishes and not continously and some others, such as sys.dm_os_wait_stats, will be updated continously.
As an example, again, sys.dm_exec_query_stats will keep the information for a particular query as long as the correlating plan is in cache (so with this said you can figure out that the contents of sys.dm_exec_cached_plan dependes on the lifetime of the cached plan).
DMv's such as sys.dm_os_wait_stats or sys.dm_os_virtual_file_stats will retain stats until server restart (with sys.dm_os_wait_stats that is true unless someone manually clears it).
Did you have a look at Performance Dashboard (for realtime diagnosing and troubleshooting) and DMVStats (tracks and stores performance data)? They are downloadable here:
DMVStats: http://www.codeplex.com/sqldmvstats
Performance Dashboard: http://www.microsoft.com/downloads/details.aspx?familyid=1d3a4a0d-7e0c-4730-8204-e419218c1efc&displaylang=en
HTH!
/Elisabeth
elisabeth@sqlserverland.com
MCITP | MCT
http://sqlblog.com/blogs/elisabeth_redei/
http://linkedin.com/in/elisabethredei
November 7, 2008 at 7:19 am
Hi Elisabeth,
Thank you very much for the information, it's greatly appreciated.
Do you happen to know the default retention policy for information returned from the DMVs, or is it a case of the DMVs will hold x number of rows of information and therefore the length of retention depends on how busy the SQL Server instance is?
As I'm collecting this information to store it in a historical database to use for route cause analysis I'm thinking of running the scripts every 5 minutes. Would you agree with this?
Thanks again,
Chris
www.sqlAssociates.co.uk
November 7, 2008 at 8:43 am
The DMV's hold information since the last time the SQL service has been restarted
November 7, 2008 at 9:30 am
Thanks for your reply, the server running SQL Server 2005 Enterprise Edition is running on an Enterprise platform and therefore SQL Server will only get restarted maybe once a year for patching. Are you saying that those DMVs will hold up to 12 months worth of information? Won't SQL Server 2005 have some sort of retention policy to stop the database from growing to an enormous size?
Many thanks,
Chris
www.sqlAssociates.co.uk
November 7, 2008 at 9:50 am
Hi Chad,
Can you pass me a list of the DMV's that your are currently working with and I can give you details for each of them (assuming you are not using each and every one of them 😉 )?
Did you have time to look at DMVStats? It was developed byt Microsoft SQLCAT tema and it really does everything you are trying to accomplish.
🙂
/Elisabeth
elisabeth@sqlserverland.com
MCITP | MCT
http://sqlblog.com/blogs/elisabeth_redei/
http://linkedin.com/in/elisabethredei
November 7, 2008 at 11:43 am
I use sys.dm_db_index_usage_stats to find obselete indexes that have not been accessed and are unneeded overhead in the database.
I also use sys.dm_db_missing_index_group_stats and sys.dm_db_missing_index_details to identofy indexes that are missing that should be created.
I just downloaded DMVStats today and installed it just before lunch so I haven't had a chance to really play around yet
November 8, 2008 at 5:04 am
Chad Churchwell (11/7/2008)
I use sys.dm_db_index_usage_stats to find obselete indexes that have not been accessed and are unneeded overhead in the database.I also use sys.dm_db_missing_index_group_stats and sys.dm_db_missing_index_details to identofy indexes that are missing that should be created.
Index usage information contains information since the last server start. Missing indexes contains a max of 500 (I think) rows and will have the most expensive 'missing' indexes that the optimiser noticed since the last server start.
Some of the DMVs are cumulative since last server start (wait stats and file stats are examples) some show the system state at the time that they are queried (exec requests, exec sessions, exec cached plans), some have other retention periods. Books online does have info for each DMV that explains how long the data is kept and when it's added. eg for missing index details, Bol says
Information returned by sys.dm_db_missing_index_details is updated when a query is optimized by the query optimizer, and is not persisted. Missing index information is kept only until SQL Server is restarted.
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
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply