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

Read more: SQL Server curry

Posted via email from .NET Info