TSQL Tutorial : How to find long running queries

By  below two methods you can find long running queries….

Method 1:

DBCC FREEPROCCACHE — DBCC command will clean the buffer.

SELECT DISTINCT TOP 1
    est.[text] AS SQLStatement,
    eqs.execution_count AS SQLExecutionCount,
    eqs.max_elapsed_time AS SQLMaximumElapsedTime,
    ISNULL(eqs.total_elapsed_time / eqs.execution_count, 0) AS SQLAverageElapsedTime
FROM sys.dm_exec_query_stats eqs CROSS APPLY sys.dm_exec_sql_text(eqs.sql_handle ) est
ORDER BY eqs.max_elapsed_time DESC
GO

Method 2:                                     

SELECT * FROM master..sysprocesses WHERE status = ‘runnable’ ORDER BY cpu desc;

DBCC INPUTBUFFER (spid) — SPID from the above query result output