Find Longest Executing Queries in SQL Server (2005,2008)

  • February 8, 2017
  • Posted by: user
  • Category: Uncategorized
No Comments

I have been working with queries today and wanted to find some queries which was very high on its execution time. There was a need in the project to find out which query is running longest in sql server to avoid time outs and to optimize those queries for better performance.

See the script below and definately it will be really helpful to you for better performing backend.

 

[code:sql]
DBCC FREEPROCCACHE
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
[/code]

Reference : Pinal Dave’s Blog