data-konzept

'Have you ever wondered how to find the worst performing TSQL queries on your instance? If you have, you are not alone. 

SQL Server maintains query statistics as TSQL commands are run. These query statistics can be returned by using the Dynamic Management Objects provided in SQL Server.

The Dynamic Management Objects sys_dm.exec_query_stats returns a view of the query statistics that SQL Server is currently maintaining. The statistics that this view returned are stored in memory with the cached plan for each query. As long a TSQL execution plan is stored in the plan cache you can return statistics for the TSQL command. Once the command is removed from the procedure cache, then the statistics are also dropped.'...

http://www.databasejournal.com/tips/finding-the-worst-performing-tsql-statement.html