See what queries are currently running

Category : Tips User Rating : 4.5 Stars      Views : 5 Stars
One Click Feedback
Please help us to improve the site by rating the quality of this article by clicking a button below.




Sometimes it is useful to see what is currently running on a SQL server. On SQL Server 2005 and later this is fairly easy to achieve using the Dynamic Management View dm_exec_requests :
SELECT * FROM sys.dm_exec_requests
This has a row for each request that is currently executing. Many of these will be internal sessions used by SQL Server but it will also include SQL commands currently executing. To filter out the internal sessions ignore any with a session_id of 50 or less.

The query can be extended to display the actual SQL executing, using the dm_exec_sql_text DMV, which is called using the APPLY operator :
SELECT      r.start_time [Start Time],session_ID [SPID],
            DB_NAME(database_id) [Database],
            SUBSTRING(t.text,(r.statement_start_offset/2)+1,
            CASE WHEN statement_end_offset=-1 OR statement_end_offset=0
            THEN (DATALENGTH(t.Text)-r.statement_start_offset/2)+1
            ELSE (r.statement_end_offset-r.statement_start_offset)/2+1
            END) [Executing SQL],
            Status,command,wait_type,wait_time,wait_resource,
            last_wait_type
FROM        sys.dm_exec_requests r
OUTER APPLY sys.dm_exec_sql_text(sql_handle) t
WHERE       session_id != @@SPID -- don't show this query
AND         session_id > 50 -- don't show system queries
ORDER BY    r.start_time
I’ve reduced the number of columns that are displayed to make things a little clearer, but added in the SQL command executed, where there is one. I’ve also eliminated any system sessions and the current session (SPID) being used to run this query.

That’s about it really !

One thing you might occasionally get is the following error message :
Msg 321, Level 15, State 1, Line 8
"sql_handle" is not a recognized table hints option. If it is intended as a parameter to a table-valued function or to the CHANGETABLE function, ensure that your database compatibility mode is set to 90.
As I mentioned DMVs were introduced in SQL Server 2005, so the queries won’t work with earlier versions. However even if you are running SQL Server 2005 it’s possible that the compatibility mode of the database you are running the query against is SQL 2000 or earlier. In this case the OUTER APPLY syntax won’t work. Fortunately this is easy to resolve by running the query against the master database or another database that has 90 or 100 compatibility – the query is not database specific so it won’t affect the results.

Related Articles

The following articles may also be of interest :

Link back to this article : http://www.sqlmatters.com/Articles/See what queries are currently running.aspx

Keywords

TSQL,query,queries,active,running


Comments
Post by aravindh on Fri 02 May 2014 17:45. Report Inappropriate Post

Changing compatibility level at production environment is disastrous. Applications connected to the databases may not work properly.
Post by Chris on Tue 27 Oct 2015 09:25. Report Inappropriate Post

Is there anyway to see what code is been run?
Post by Lok on Wed 26 Oct 2016 12:36. Report Inappropriate Post

Really awesome! Thanks.

Post a comment   No login required !

 
Name :   Email :   Website :  
Will be displayed alongside your comment
Not displayed Optional, but displayed if entered