Technical FAQs

Ask a Question

How to view what queries are currently running on SQL Server

Issue
Inspecting the currently running SQL queries can be very useful for debugging issues on your database servers.
Often, viewing the queries which are executing when a problem is occurring (ie. a query is running slowly) will help to identify the cause of the problem.

Product Line
Struxureware Power Monitoring
Power Monitoring Expert
ION Enterprise
ION EEM

Environment
SQL Server 2005, SQL Server 2008 R2, SQL Server 2012, SQL Server 2014, SQL Server 2016

Cause
Troubleshooting possible system issues

Resolution
*Warning: Irreparable database damage can occur. This procedure should only be performed by users familiar with SQL Server Management Studio. Databases should be backed up prior to performing this procedure.*

On SQL Server 2005 and later this can be achieved using the Dynamic Management Views.  
DMVs were introduced in SQL Server 2005, so the queries won’t work with earlier versions.

The following query will return 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.

SELECT * FROM sys.dm_exec_requests

This query can be further enhanced as below, to display the actual SQL statements executing by combining the dm_exec_sql_text DMV.

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

* Alternatively, the attached sql script can be run to obtain this information.

Was this helpful?
What can we do to improve the information ?