Wednesday, June 17, 2015

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

No comments:

Post a Comment