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?
Best Answer
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.