July 25, 2012 at 2:29 am
I'd like to know if theres any way to clear out the dmv stats tables as I want to start with a fresh set of stats in order to monitor long running queries.
I picked up a query from Ben-gan's T-SQL Querying for SQL 2008 (page 167), that retrieves data from the sys.dm_exec_query_stats, sys.dm_exec_sql_text and sys.dm_exec_plan_attributes tables, unfortunately the numbers are skewed due to the Execcution count stat getting reset but the total_elapsed_time not.
I proved this by restarting the SQL server instance and running a know query, that takes about 3.4 minutes, the Exec count is shown as 1 yet the average total_elapsed_time is 169731445 ms, or just over 47 hours.
The reason for this is that I'd like to flag possible problem queries and get developers to tune them before they are deployed on site, so I'd ideally like to start with a clean sheet in regards to timings for queries.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
July 25, 2012 at 2:39 am
Jason-299789 (7/25/2012)
I'd like to know if theres any way to clear out the dmv stats tables as I want to start with a fresh set of stats in order to monitor long running queries.I picked up a query from Ben-gan's T-SQL Querying for SQL 2008 (page 167), that retrieves data from the sys.dm_exec_query_stats, sys.dm_exec_sql_text and sys.dm_exec_plan_attributes tables, unfortunately the numbers are skewed due to the Execcution count stat getting reset but the total_elapsed_time not.
I proved this by restarting the SQL server instance and running a know query, that takes about 3.4 minutes, the Exec count is shown as 1 yet the average total_elapsed_time is 169731445 ms, or just over 47 hours.
The reason for this is that I'd like to flag possible problem queries and get developers to tune them before they are deployed on site, so I'd ideally like to start with a clean sheet in regards to timings for queries.
:Whistling:
Ignore me, just waiting for the morning caffine fix to kick in, and I've found the timings are actually in microseconds not milliseconds so I shuld be dividing by 1,000,000 not 1,000.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
July 25, 2012 at 12:15 pm
From the perspective of taking this discussion further it seems like other than a couple of DMVs the only way to rest DMV is to restart SQL Services which is not a viable option.
Following is a link to a URL for a similar discussion:
http://www.sqlservercentral.com/Forums/Topic899837-146-1.aspx#bm936153
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply