Finding Longest running query in SQL Server/MSSQL

In MySQL we use 'show full processlist' command to find if there is any query running for long time. This has helped me in some MySQL setups and I am able to find the problematic MySQL queries which is affecting the application performance.

However, in MSSQL this has been challenge as I am not able to find any straight forward solution in the SQL studio/Query.

Of late, for my MSSQL related queries I look up to one blog where I find almost all answers to my MSSQL problems. That is http://blog.sqlauthority.com/ .

This is been a excellent source of information for me and I hope all others who visited his blog might feel the same as well. Thanks Pinal Dave.

I have pasted his query which helped me to find the QUERY WHICH IS RUNNING for LONG time.

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

More info on the following links of his blog.

http://blog.sqlauthority.com/2009/01/02/sql-server-2008-2005-find-longest-running-query-tsql/
http://blog.sqlauthority.com/2009/01/23/sql-server-2008-2005-find-longest-running-query-tsql-part-2/

Cheers !!
Krishna.