Long Running Queries
A long running queries report has been added to the Server Health reports. With this report you can track down time consuming queries that may be causing excessive load on your server.
This report includes the long runing queries that are still in the procedure cache. Restarting the server or flushing the procedure cache will clear this list of long running queries and start with a clean list. To clear the procedure cache run DBCC FREEPROCCACHE; but use caution on production servers as this will flush all parsed queries from the procedure cache.
To use the report, download the Database Health SSRS project, and run the LongRunningQueries report. Be sure to adjust the shared datasource to point to your database.
To see the full report, download the LongRunningQueries.pdf