Find Current Running SQL Statement in SQL Server

By: Tim Ford   |   Comments (5)   |   Related: > 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] > 0 THEN  
        --The start of the active command is not at the beginning of the full command text 
        CASE SDER.[statement_end_offset]  
           WHEN -1 THEN  
              --The end of the full command is also the end of the active statement 
              SUBSTRING(DEST.TEXT, (SDER.[statement_start_offset]/2) + 1, 2147483647) 
           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 -1 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.

About the author

Tim Ford is a Senior Database Administrator with MindBody.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips