Find the Most Time Consuming Code in SQL

---

To find the the Top 10 queries that take the most time run the query:

select top 10 source_code,stats.total_elapsed_time/1000000 as seconds,last_execution_time

from sys.dm_exec_query_stats as statscross

apply(SELECT text as source_code FROM sys.dm_exec_sql_text(sql_handle)) AS query_textorder by total_elapsed_time desc

To find the top 10 queries that take the maximum physical_reads run the query:

select top 10 source_code,stats.total_elapsed_time/1000000 as seconds,last_execution_time

from sys.dm_exec_query_stats as statscross

apply(SELECT text as source_code FROM sys.dm_exec_sql_text(sql_handle)) AS query_textorder by total_physical_reads desc

The method would work from version 2005 onwards. If you use it make sure the compatibility mode is set to 90.

---

---