Admin User Feb,08 2017
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.
DBCC FREEPROCCACHESELECT DISTINCT TOP 10t.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 FrequencyPerSecFROM sys.dm_exec_query_stats sCROSS APPLY sys.dm_exec_sql_text( s.sql_handle ) tORDER BYs.max_elapsed_time DESCGO
Reference : Pinal Dave's Blog