Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question

migrated from stackoverflow.com Sep 25 '12 at 16:09

This question came from our site for professional and enthusiast programmers.

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.