Find Longest Running Query

DBCC FREEPROCCACHE

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

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