Showing All Current T-SQL Executions in the database

We would like to see if there exists a query running/suspended in the database without using SQL Activity Monitor. The query also works in SQL Azure.

In order to do that, we used the following query:

SELECT r.total_elapsed_time,text, task_state
   sys.dm_exec_sessions s
   LEFT  JOIN sys.dm_exec_connections c
        ON  s.session_id = c.session_id
   LEFT JOIN sys.dm_db_task_space_usage tsu
        ON  tsu.session_id = s.session_id
   LEFT JOIN sys.dm_os_tasks t
        ON  t.session_id = tsu.session_id
        AND t.request_id = tsu.request_id
   LEFT JOIN sys.dm_exec_requests r
        ON  r.session_id = tsu.session_id
        AND r.request_id = tsu.request_id
   OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) TSQL
   where r.total_elapsed_time >1

I only select relevant columns for me and an elapsed time filter.


No comments:

Post a Comment