SQL SERVER – 2008 – 2005 – Find Longest Running Query – TSQL

 SQL SERVER – 2008 – 2005 – Find Longest Running Query – TSQL

I always run following DBCC command before I started to use my query. Following DBCC commands clears the cache of the server and starts fresh logging of the query running time.

1	DBCC FREEPROCCACHE

Run following query to find longest running query using T-SQL.

1 2 3 4 5 6 7 8 9 10 11 12

SELECT DISTINCT TOP 10 t.TEXT QueryName, s.execution_count AS ExecutionCount, s.max_elapsed_time AS MaxElapsedTime, ISNULL(s.total_elapsed_time / s.execution_count, 0) AS AvgElapsedTime, s.creation_time AS LogCreatedOn, ISNULL(s.execution_count / DATEDIFF(s, s.creation_time, GETDATE()), 0) AS FrequencyPerSec FROM sys.dm_exec_query_stats s CROSS APPLY sys.dm_exec_sql_text( s.sql_handle ) t ORDER BY s.max_elapsed_time DESC GO