statement_start_offset: Showing the Exact Statement Running Now

statement_start_offset tells you where the statement running right now begins inside a long batch. Together with the end offset, it lets you pull out that one statement instead of reading three hundred lines of procedure text.

The 2 AM question: which line is slow?

Picture the page. A stored procedure has been running for forty minutes. You look at sys.dm_exec_requests and ask for the text. SQL Server hands you the whole procedure, every line of it. Somewhere in there is the statement that is actually busy.

The request row has two helpers for this: statement_start_offset and statement_end_offset. They mark the running statement inside the batch text. You just have to convert them correctly.

Let a query find itself

I need a live request to look at, and I only have one connection. So the query inspects its own session. The demo procedure has three statements. The third one reads sys.dm_exec_requests for @@SPID.

The text comes from sys.dm_exec_sql_text, using the sql_handle of the same request row. Never pair offsets from one request with text from another source. You would get a convincing substring of something unrelated.

CREATE OR ALTER PROCEDURE dbo.OffsetDemo
AS
BEGIN
    SELECT COUNT(*) AS object_count FROM sys.all_objects;

    SELECT COUNT(*) AS column_count FROM sys.all_columns;

    SELECT r.session_id, r.status,
           r.statement_start_offset, r.statement_end_offset,
           SUBSTRING(t.text, (r.statement_start_offset / 2) + 1,
               ((CASE WHEN r.statement_end_offset = -1
                      THEN DATALENGTH(t.text)
                      ELSE r.statement_end_offset END
                 - r.statement_start_offset) / 2) + 1) AS CurrentStatement,
           LEN(t.text) AS full_text_chars
    FROM sys.dm_exec_requests AS r
    CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
    WHERE r.session_id = @@SPID;
END;
GO
EXEC dbo.OffsetDemo;

The first two results are the counts. The third shows one row with status running. CurrentStatement starts at the third SELECT, the one that was executing. It does not include the two counts above it. The full text of the procedure is longer, and full_text_chars says by how much.

The offsets depend on the exact text, including spaces and line breaks, so yours will differ from mine. On a busy server, change the filter to r.session_id <> @@SPID and you will see everyone else’s running statements.

Why you divide by two

The offsets count bytes, but nvarchar uses two bytes per character. SUBSTRING counts characters from one. So the start is offset divided by two, plus one. The length is end minus start, divided by two, plus one.

Here is a handmade batch with two statements and three trailing spaces. I point at the second statement. The correct column returns SELECT N’beta’; with its trailing spaces. The brackets make them visible.

Skip the divide on the start and you get a single blank space. It looks like a server bug. It is a query bug.

DECLARE @batch nvarchar(max) = N'SELECT N''alpha''; SELECT N''beta'';   ';
DECLARE @start int = DATALENGTH(N'SELECT N''alpha''; ');
DECLARE @end int = -1;

SELECT @start AS statement_start_offset,
       @end AS statement_end_offset,
       '[' + SUBSTRING(@batch, @start / 2 + 1,
           (CASE WHEN @end = -1 THEN DATALENGTH(@batch) ELSE @end END
            - @start) / 2 + 1) + ']' AS Correct,
       '[' + SUBSTRING(@batch, @start + 1, 100) + ']' AS ForgotToHalve,
       DATALENGTH(@batch) AS BatchBytes,
       LEN(@batch) AS LenChars;

The minus one case

When the end offset is -1, the statement runs to the end of the batch. Treat -1 as a real offset and the length goes negative. SUBSTRING answers with error 537.

That is why the formula swaps -1 for DATALENGTH of the text. LEN is the wrong tool here. In the result above, LenChars is 32 while the batch holds 70 bytes. LEN ignores trailing spaces and counts characters, not bytes.

I never saw -1 on my demo server, where the live rows always had a real end offset. So I test that branch with a handmade batch, as above.

DECLARE @batch nvarchar(max) = N'SELECT N''alpha''; SELECT N''beta'';   ';
DECLARE @start int = DATALENGTH(N'SELECT N''alpha''; ');
DECLARE @end int = -1;

BEGIN TRY
    SELECT SUBSTRING(@batch, @start / 2 + 1, (@end - @start) / 2 + 1) AS Wrong;
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER() AS error_number, ERROR_MESSAGE() AS error_message;
END CATCH;

Cached statements use the same math

sys.dm_exec_query_stats keeps the same two offsets for statements that finished and stayed in the plan cache. Apply the identical expression to the text for its sql_handle. The demo procedure now shows one row per statement, each with its own text.

Read this one as history. A cached row says the statement ran, not that it is running now. Check execution_count and when the plan was created before you trust a total.

SELECT qs.statement_start_offset, qs.statement_end_offset, qs.execution_count,
       SUBSTRING(t.text, (qs.statement_start_offset / 2) + 1,
           ((CASE WHEN qs.statement_end_offset = -1
                  THEN DATALENGTH(t.text)
                  ELSE qs.statement_end_offset END
             - qs.statement_start_offset) / 2) + 1) AS StatementText
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS t
WHERE t.objectid = OBJECT_ID(N'dbo.OffsetDemo')
  AND t.dbid = DB_ID()
ORDER BY qs.statement_start_offset;

Use the statement, but keep the batch

Once you know the statement, check its wait, its blocking, and its plan. A line waiting on a lock needs a different fix than one burning CPU. Keep the full batch too. Variables, temp tables, and earlier assignments often explain the behavior. Parameter values will not appear in the text.

Text can also be missing, for example with an encrypted module. CROSS APPLY drops those rows. OUTER APPLY keeps them with a NULL, which is honest. Never replace a missing statement with a guess.

The last block removes the demo procedure.

DROP PROCEDURE IF EXISTS dbo.OffsetDemo;

Next time the page comes in, you will know which line to chase first.

The batch text is not the current statement, it is only the container.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.