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.