Hi i am working on sql server 2008 r2, i have a situation where i wanted to capture the DMV's data into a table, i would like to know when DMV's tables get refreshed or data flushed out. because as soon as the run going on at the production environment the DMV's data changes. i wanted to capture each data that comes in the DMV's can somebody please help me to capture this data i need this in production to look on CPU,Reads,writes for the data or the query that are running by the testing team. Thanks! in advanced
1 Answer 1
The clue is in the name, DMVs are Dynamic Management Views. The data they return is dynamic and will be the latest values available to the system, so they will be constantly updating themselves. So the best you could do is to sample them at a set interval and then try to extrapolate the intermdiate values if you really need them
Some of the DMVs do capture cumulative statistics (sys.dm_exec_query_stats for example, captures cumulative stats about queries). But they don't offer the sort of access you might be looking for at the individual query level.
Your other option may be to use Extended Events ( http://msdn.microsoft.com/en-us/library/bb630354%28v=sql.105%29.aspx ) to capture the queries that the devs are submitting. Pinal Dave provides a nice example of capturing queries - http://blog.sqlauthority.com/2010/03/29/sql-server-introduction-to-extended-events-finding-long-running-queries/ - his is based on capturing long running queries, but you could easily reduce the time critreia for capture, or remove it completely in favour of a login match or applictaion match as required.