- February 8, 2017
- Posted by: user
- Category: Uncategorized
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.
SELECT DISTINCT TOP 10
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
Reference : Pinal Dave’s Blog