Find the Most Time Consuming Code in your SQL Server Database

This post will demonstrate how to find T-SQL code (SQL Server 2005/2008) that takes the most time to execute. Note that a time consuming code may not necessarily be inefficient; it also depends on the volume of data being processed.

--Top 10 codes that takes maximum time
select top 10 source_code,stats.total_elapsed_time/1000000 as seconds,
last_execution_time from sys.dm_exec_query_stats as stats
cross apply
(SELECT text as source_code FROM sys.dm_exec_sql_text(sql_handle))

AS query_text
order by total_elapsed_time desc

--Top 10 codes that takes maximum physical_reads
select top 10 source_code,stats.total_elapsed_time/1000000 as seconds,
last_execution_time from sys.dm_exec_query_stats as stats
cross apply
(SELECT text as source_code FROM sys.dm_exec_sql_text(sql_handle))
AS query_text
order by total_physical_reads desc

The sys.dm_exec_query_stats is a Dynamic Management view that gives the statistical information's about cached data. The sys.dm_exec_sql_text is the another view that gives actual text of the sql_handle which is in binary format.

The first query sorts data based on descending order of total_elapsed_time and second query by total_physical_reads.

Similarly also read:

Identify Memory and Performance Issues in T-SQL Queries and Fix them 

and

Monitor Running Processes in SQL Server 2005/2008