asked
Cycle SQL Statistics
How can I go about cycling all the sql stats which are stored in a DMV? I want to see how the database is performing since changes were made to many procedures so I want a fresh start, is there any way to go about this without restarting the instance? Or is there a better way to go about this
It completely depends on the DMV in question. For example, sys.dm_exec_query_stats is completely dependent on the cache. If you wanted to, you could clear it out by issuing DBCC FREEPROCCACHE. But, before you run off and do that to the production machine, remember, that means that every query in the system will have to recompile. sys.dm_index_usage_stats is only reset when the server restarts or a database is detached.
Yeah, pretty much. As well as dump all the users off the system. Is there a particular DMV that you want to reset? What about just using a mechanism of capturing the data before and after your test run?
Ok I have a query that uses sys.dm_exec_query_stats and sys.dm_exec_sql_text, and checks for long running queries and Costly I\O queries. What type of mechanism are you referring to you suggest? I can run a query and attach and a column for getdate() and run it as a job.
Let's say you run this every five minutes, you'd want to see any queries that have been called in the last five minutes. You can do that using the last_execution_time. But, the max run time value could be the same as the previous five minutes. So, what you'd need to do is dump the stuff to a table ever five minutes and then compare the current state with the table so you can identify both queries that have been called in the last five minutes and have changed their max run time. Why not just use extended events to capture long running queries. It's much easier.
15 People are following this question.
