Return currently long running queries in sql server

Question

I'm using the following SQL query:

SELECT status, total_elapsed_time, text 
FROM sys.dm_exec_requests CROSS APPLY 
     sys.dm_exec_sql_text(sql_handle) 
WHERE status = 'running' AND 
      text like '%Insert%' AND 
      total_elapsed_time > '5'

To check if specific running query that contains 'Insert' in the text and her total_elapsed_time value is more then 5ms.

But when i'm manually running the query is SSMS, i'm always getting different output.

Don't i need to get a fixed output instead (which match the condition of my query of course..)?

Is there any other recommend way to check if there is a currently long running query on my SQL Server?

Answer 1

First of all, I would suggest to exclude your own query from the resultset. Furthermore, in order to receive the same order, you should include an ORDER BY. Something like the following might do the trick:

SELECT STATUS
    ,total_elapsed_time
    ,TEXT
FROM sys.dm_exec_requests a
CROSS APPLY sys.dm_exec_sql_text(sql_handle) b
WHERE STATUS = 'running'
    AND TEXT LIKE '%Insert%'
    AND text NOT LIKE 'SELECT STATUS%TOTAL_ELAPSED_TIME%sys.dm_exec_requests%'
    AND total_elapsed_time > '5'
ORDER BY a.start_time DESC
        ,a.statement_start_offset DESC
        ,a.statement_end_offset DESC
        ,total_elapsed_time DESC