SQL Server - Find Longest Running Query

[Mohammed Ishtiyaq]

Today I have been asked to find the longest running query of one of our production server. It was interesting and I have given the longest running query to concern person but at the same time I thought that how many ways do I have to get it. I have found three different ways to get the same result. And they are:

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


Method 3:
DECLARE @p_handle binary(20);

SELECT @p_handle = sql_handle FROM master..sysprocesses
where spid = (select top 1 spid from sys.sysprocesses ORDER BY cpu desc);

SELECT [text] FROM ::fn_get_sql(@p_handle);

[Erland Sommarskog]

Mohammed Ishtiyaq (md.is...@gmail.com) writes:
> Today I have been asked to find the longest running query of one of our
> production server. It was interesting and I have given the longest
> running query to concern person but at the same time I thought that how
> many ways do I have to get it. I have found three different ways to get
> the same result. And they are:
>
> Method 1:
> DBCC FREEPROCCACHE -- DBCC command will clean the buffer.
>

So that clears the plan cache, which is a good way to slow down
your server.

But else your query is a reasonable one, although I would rather look at
total_elapsed_time.

> Method 2:
> SELECT * FROM master..sysprocesses WHERE status = 'runnable' ORDER BY cpu
> desc;
> DBCC INPUTBUFFER (spid) -- SPID from the above query result output
>

That gives the process that has spent the most CPU, but it that process
has been logged for serveral months, that does not say much.

> Method 3:
> DECLARE @p_handle binary(20);
>
> SELECT @p_handle = sql_handle FROM master..sysprocesses
> where spid = (select top 1 spid from sys.sysprocesses ORDER BY cpu desc);
>
> SELECT [text] FROM ::fn_get_sql(@p_handle);
>

The same issue here. Add to this that both sysprocesses and fn_get_sql
are deprecated.


--
Erland Sommarskog, Stockholm, esq...@sommarskog.se