March 22, 2011 at 1:33 pm
Hi – I have a question regarding SQL Server 2005/2008 DMV. I would like to know what is the duration of the data retrieved by the DMV’s? For example, if I query the following DMV to know SQL Server memory usage (i.e. sys.dm_os_memory_objects), will it provide the current utilization or any particular historical information. Whether we can customize SQL to store the historical information for a particular period? thanks!
March 22, 2011 at 1:42 pm
It depends on the DMV. Many of the most interesting ones about query performance are completely dependent on the cache. Others work off the last time SQL Server was restarted or the OS was restarted. It just depends. You need to check the Books Online for each one to know for sure.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 22, 2011 at 1:52 pm
Thanks for your immediate response. Actually, I am in the process collecting performance data of a SQL Server particularly used for reporting. I was using ‘Performance Dashboard’ to figure out slow running queries By CPU, By Logical Read, By Logical Writes, By Duration etc. I want to know whether I can collect the data for last 24 hours somehow? Any idea?
March 22, 2011 at 2:03 pm
The query stats is completely dependant on the plan cache. When something is thrown out of cache the stats disappear.
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
March 22, 2011 at 2:09 pm
Ok, I would like to be little specific here. Can I collect 'expensive quries by CPU' data for last 24 hours by using a DMV? If yes, could you please let me know how to do this? Many thanks!
March 22, 2011 at 2:17 pm
Not with 100% reliability. You could miss queries, you could get distorted stats because of compiles/recompiles/plans discarded from cache.
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
March 22, 2011 at 3:41 pm
sppdba (3/22/2011)
Ok, I would like to be little specific here. Can I collect 'expensive quries by CPU' data for last 24 hours by using a DMV? If yes, could you please let me know how to do this? Many thanks!
You won't know what's not there. It really depends on the volatility of your cache. You might have queries that sit in cache for days. You might have queries that sit in cache for minutes. There is simply no way to know for sure.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply