Script to get Long Running queries, current running queries in SQL Server

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
GO