Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

What SQL Statements Are Currently Executing?

By Ian Stirk, (first published: 2008/10/28)

Introduction

sp_who2 is a well known utility that shows what spids are currently executing. However the information it shows is relatively limited. For example, it only shows the type of command executing as SELECT, DELETE etc, with no reference to the actual underlying SQL executing.

Knowing what SQL is executing can be vital in debugging why a query is taking a long time, or determining if it is being blocked. It can also be useful in showing the progress of a stored procedure i.e. what statement within the stored procedure is currently executing.

The utility described in this article will obviate these limitations of sp_who2.

The utility makes use of Dynamic Management Views (DMVs), so can be used by SQL Server 2005 or greater.

What SQL Statements Are Currently Executing Utility

The SQL used in this utility ‘dba_WhatSQLIsExecuting’ is given in Listing 1.

The Dynamic Management View (DMV) sys.db_exec_requests shows which requests are currently executing, the information shown includes the handle to the whole SQL text of the batch or stored procedure (sql_handle), together with offsets relating to the section of SQL within the batch that is currently executing (statement_start_offset and statement_end_offset).

To determine the current section of SQL currently executing, we need to call the Dynamic Management Function (DMF) sys.dm_exec_sql_text, passing in the handle of the SQL batch that is currently executing, and then apply the relevant offsets.

We can get more information about the query by combining the sys.db_exec_requests DMV with the sys.processes system view (joined on spid/session_id). This information includes who is executing the query, the machine they are running from, and the name of the database.

The utility selects relevant fields from the sys.db_exec_requests and sys.sysprocesses views. The selected fields are described in figure 1 (largely taken from SQL Server 2005 Books online).

Column name Data type Description
spid smallint SQL Server process ID.
ecid smallint Execution context ID used to uniquely identify the subthreads operating on behalf of a single process.
dbid smallint ID of the database currently being used by the process.
nt_username nchar(128) Windows user name for the process, if using Windows Authentication, or a trusted connection.
status nchar(30) Process ID status. For example, running and sleeping.
wait_type bigint Current wait time in milliseconds.
Individual Query varchar SQL Statement currently running.
Parent Query varchar Routine that contains the Individual Query.
program_name nchar(128) Name of the application program.
Hostname nchar(128) Name of the workstation.
nt_domain nchar(128) Microsoft Windows domain for the client, if using Windows Authentication, or a trusted connection.
Start_time datetime Time when the request is scheduled to run.

Figure 1 Columns in the ‘What SQL Statements Are Executing’ utility.

Running the utility on my SQL Server gives the results given in Figure 2.

Results

Figure 2 Output from the ‘What SQL Statements Are Executing’ utility.

The results show the Parent Query that is running (typically a stored procedure), together with the Individual Query within the Parent Query that is currently executing. Additional useful information (e.g. database name, user name etc) is also shown.

Discussion

This utility allows you to observe the progress of a stored procedure or SQL batch, additionally it can be used to identify the cause of a long running query or blocking query.

Since the utility uses existing data held in DMVs it is relatively non-intrusive and should have little affect on performance.

If the identified queries are long running or causing blocking, it might be worthwhile running them inside the Database Tuning Advisor (DTA), this might identify the cause of the slow running (e.g. a missing index).

Further work

It is possible to extend this utility to report only on the database you are interested in, by providing a filter based on database name or database id.

It might be interesting to use the output to drive a trace and/or process-flow engine. This will report on process flow through a stored procedure, and could be useful in determining how much code has been hit/missed during testing, as well as getting a view on what code is executed for a given run/set of parameters.

Conclusion

The utility described in this article will allow you to identify what SQL statements are currently executing. This information can be useful in debugging the cause of both long running queries and blocking, and should prove valuable in the everyday work of the SQL Server DBA/developer.

Credits

Ian Stirkhas been working in IT as a developer, designer, and architect since 1987. He holds the following qualifications: M.Sc., MCSD.NET, MCDBA, and SCJP. He is a freelance consultant working with Microsoft technologies in London England. He can be contacted at Ian_Stirk@yahoo.com.

Code

CREATE PROC [dbo].[dba_WhatSQLIsExecuting]
AS
/*--------------------------------------------------------------------
Purpose: Shows what individual SQL statements are currently executing.
----------------------------------------------------------------------
Parameters: None.
Revision History:
	24/07/2008  Ian_Stirk@yahoo.com Initial version
Example Usage:
	1. exec YourServerName.master.dbo.dba_WhatSQLIsExecuting               
---------------------------------------------------------------------*/
BEGIN
    -- Do not lock anything, and do not get held up by any locks.
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

    -- What SQL Statements Are Currently Running?
    SELECT [Spid] = session_Id
	, ecid
	, [Database] = DB_NAME(sp.dbid)
	, [User] = nt_username
	, [Status] = er.status
	, [Wait] = wait_type
	, [Individual Query] = SUBSTRING (qt.text, 
             er.statement_start_offset/2,
	(CASE WHEN er.statement_end_offset = -1
	       THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
		ELSE er.statement_end_offset END - 
                                er.statement_start_offset)/2)
	,[Parent Query] = qt.text
	, Program = program_name
	, Hostname
	, nt_domain
	, start_time
    FROM sys.dm_exec_requests er
    INNER JOIN sys.sysprocesses sp ON er.session_id = sp.spid
    CROSS APPLY sys.dm_exec_sql_text(er.sql_handle)as qt
    WHERE session_Id > 50              -- Ignore system spids.
    AND session_Id NOT IN (@@SPID)     -- Ignore this current statement.
    ORDER BY 1, 2
END

Listing 1 shows the code for the ‘What SQL Statements Are Executing’ utility.

Resources:

What SQL statement is running being executed-SSC.doc
Total article views: 47821 | Views in the last 30 days: 58
 
Related Articles
FORUM

query execution order

query execution order

FORUM

current sessions in database

current sessions in database

FORUM

cancel executing query

cancel executing query

FORUM

Select query to retrieve data based on current date and time

SQL query to retrieve current data from database based on the current date and time

FORUM

Estimation of Query Execution Time

Query Execution Time.

Tags
administration    
dmv    
monitoring    
performance monitoring    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones