In SQL Server 2008 R2, admin users are allowed to view the Activity Monitor and under "Recent Expensive Queries", there is a list of SQL statements being ran on the server.

Is there a way to export each and every one of the SQL statements displayed without using SQL Server profiler?

I was given the responsibility of monitoring a database with many redundant tables and I have no idea which tables are actually in use.

I want to remove those dummy or unused tables and retain the productive tables.

Any suggestions on how to accomplish this?

  • 3
    I'm pretty sure the Activity monitor just pulls the root of its data from sys.dm_exec_query_stats (and obviously with sys.dm_exec_sql_text). As for a way to directly import it form Activity Monitor? I don't think that's possible. But you could just work with the direct DMV to get the data. – Thomas Stringer Apr 6 '12 at 12:40

Well you can (on a dev system of course) run a trace against yourself running activity monitor and then isolate the queries you want to use for monitoring. Then you could either create custom jobs or use custom collectors with management data warehouse to execute those queries on your desired schedule to log the activity you want to another table. Then you can just analyze the results there.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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