Learn more about SQL Server tools

   
   






















































Connect with MSSQLTips




MSSQLTips Facebook Page MSSQLTips LinkedIn Page MSSQLTips RSS Feed MSSQLTips Pinterest Page MSSQLTips Twitter Page MSSQLTips Google+ Page

How to isolate the current running commands in SQL Server

MSSQLTips author Tim Ford By:   |   Read Comments (2)   |   Related Tips: More > Monitoring

Problem
My previous article concerned querying the Dynamic Management Objects to return the last query to execute for a selected session.  What if you are interested in the current state of activity on your SQL instance though?  Examining what has already occurred, while valuable in some cases is most likely not as important as what is currently taxing your server.  This being the case, how do you examine the currently executing statement for each of the requests issued against your SQL Server instance?

Solution
In the last tip I explained that a session, in SQL Server terminology, is another name for a user process.  Think of a request as just that: a request made by a user within the context of a session on the SQL Server instance.  In order to query the requests currently in-state on a SQL Server you'll need to access the sys.dm_exec_requests Dynamic Management View.  For the context of this article we are interested in just a small subset of the columns exposed by this DMV:

  • sql_handle - the handle (a.k.a. unique identifier) of the SQL command text for the request.  This encompasses the full query text, not just the individual command being executed at the time you query sys.dm_exec_requests.
  • statement_start_offset - this is the character location for the start of the active command being executed.
  • statement_end_offset - as expected, this is the character location for the end of the active command being executed.
  • session_id - as with sys.dm_exec_sessions (see previous tip for details) this is the unique identifier for a session across a SQL Server instance.

Before we proceed it should be noted that we are dealing with unicode strings.  This means that each character consumes two bytes of space, not just one.  The calculations in the query we will be examining shortly account for that where applicable by dividing the statement_start_offset and statement_end_offset by 2 in order to convert between the values stored in the DMV (as unicode) when parsing within the SUBSTRING() function, which expects non-unicode strings.

For example.  Let's say that for a specific record in sys.dm_exec_requests we have a statement_start_offset of 100 and a statement_end_offset of 500.  This means that the portion of the query that is currently running starts at the 100th byte (50th character) and ends at the 500th byte (250th character) of the query text associated with the request.

There is one addition item that should be noted when discussing the offset values.  Quite frequently you'll note that the statement_end_offset returns a value of -1.  This simply signifies that the end of the actively-executing statement is the end of the full query text.  In other words, the last portion of the full query text is what is currently running on the SQL Server instance.  The formula below accounts for that.  With all this being said, let's now look at the query and a sample of what to expect when reviewing results:

SELECT SDER.[statement_start_offset],  
  
SDER.[statement_end_offset]
  
CASE  
     
WHEN SDER.[statement_start_offset] THEN 
        
--The start of the active command is not at the beginning of the full command text
        
CASE SDER.[statement_end_offset] 
           
WHEN -THEN 
              
--The end of the full command is also the end of the active statement
              
SUBSTRING(DEST.TEXT, (SDER.[statement_start_offset]/2) + 12147483647)
           
ELSE  
              
--The end of the active statement is not at the end of the full command
              
SUBSTRING(DEST.TEXT, (SDER.[statement_start_offset]/2) + 1, (SDER.[statement_end_offset] SDER.[statement_start_offset])/2)  
        
END 
     ELSE 
        
--1st part of full command is running
        
CASE SDER.[statement_end_offset] 
           
WHEN -THEN 
              
--The end of the full command is also the end of the active statement
              
RTRIM(LTRIM(DEST.[text])) 
           
ELSE 
              
--The end of the active statement is not at the end of the full command
              
LEFT(DEST.TEXT, (SDER.[statement_end_offset]/2) +1
        
END 
     END AS 
[executing statement]
  
DEST.[text] AS [full statement code] 
FROM sys.[dm_exec_requests] SDER CROSS APPLY sys.[dm_exec_sql_text](SDER.[sql_handle]DEST 
WHERE SDER.session_id 50 
ORDER BY SDER.[session_id]SDER.[request_id]

Before we look at a sample result set from this query we should probably perform a quick breakdown of its structure as it pertains to parsing out the active command being executed.  We first examine where active portion of the query begins via the statement_start_offset.  If the value of the start offset does not start at 0 then we know that we will need to parse out the value of the active command via a SUBSTRING() function call.  This being the case we proceed to determine where the active statement ends.  A value of -1 for the statement_end_offset (as you recall) signifies that the end of the active statement and the end of the full query text are the same.  We can cheat a bit on the SUBSTRING() function's length parameter if this is the case by simply passing it a length that meets the max size of the data type for the query text; otherwise we need to compute the length parameter by subtracting the start offset value from the end offset value.  If our statement_start_offset is 0 then we will either return the full query text as the actively-executing portion of the text (if the start offset is 0 and the end offset is -1) or use the LEFT() function to isolate the active command when the end offset is not equal to -1 (the end of the full command.)  Of course in all our calculations we must take into consideration unicode and divide by 2 when necessary.  You will also note that I am excluding any sessions with an ID less than 50.  This filters out dedicated system sessions on the SQL Server instance.

Now that you have an idea of what I did inside the query text we will take a look at this query in use.  For the basis of this review I opened two query windows on my test SQL Server.  In one window I created the following series of Transact-SQL commands that will act as our full command text:

SELECT [CompanyName][Address][City][Region][PostalCode][Country]
FROM Northwind.dbo.Customers 
ORDER BY [City];

DECLARE @Counter INT
CREATE TABLE 
##Foo (ID INT)

SELECT @Counter =  1

WHILE @Counter 100000
  
BEGIN
     INSERT INTO 
##Foo (ID)
     
VALUES (@Counter)
     
SELECT @Counter @Counter 1
  
END;

SELECT ID 
FROM ##Foo 
ORDER BY ID;

DROP TABLE ##Foo;

The other query window is home to our first query.  If I kick off the sample query and switch over to our call against the sys.dm_exec_requests DMV I return the following results:

You'll see at this point in time the query engine has completed the call against Northwind.dbo.Customers and has moved on the preparing to run through the 100,000 step loop.  This information is far more granular than what you see if you were to simply review the Activity Monitor is SQL Server Management Studio or return the full query text for a selected request.  Isolating the actual current command that is running provides you with better insight into what may (or may not) be a root cause for performance issues currently occurring on a SQL server.

Next Steps

  • The Dynamic Management Objects have so much to offer the DBA.  Check out other tips on DMOs from MSSQLTips.com.
  • Read more tips by the author here.
  • Still interested in information on sysprocesses, whether as a system table (pre-SQL 2005) or system view?  Here are some tips that meet your needs.


Last Update: 8/7/2009


About the author
MSSQLTips author Tim Ford
Tim Ford is a Senior Database Administrator and SQL Server MVP. He has been working with SQL Server since 1999.

View all my tips
Related Resources


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Post a comment or let the author know this tip helped you.

       All comments are reviewed, so stay on subject or we may delete your comment.

*Name   *Email Notify for updates



       Note: your email address is not published. Required fields are marked with an asterisk (*)


Get free SQL tips:

*Enter Code refresh code     



Friday, May 18, 2012 - 11:24:15 AM - KateT Read The Tip

Thank you very much.  This is exactly what I was looking for.  I put this in a wait loop and excluded the sys query itself and now have visibility to whether or not my queries are progressing.


Tuesday, August 11, 2009 - 2:55:42 AM - skuljit Read The Tip

 Nice, Short and descriptive




 
Sponsor Information