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
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