#0361 – SQL Server – Script to identify the currently running T-SQL query statement


Many a times we encounter a situation wherein a stored procedure is taking a very long time to execute and the team would like to understand – whether the batch is making any progress or not and what statement is currently being executed by the database engine. I am often asked the question:

How do I know what T-SQL query statement is currently running on my SQL Server instance?

Today, I share a small query that would quickly return the following pieces of information:

  1. T-SQL batch being executed
  2. T-SQL Query statement being executed*
  3. Currently encountered Wait type
  4. Currently encountered Wait duration
  5. T-SQL Command Type
  6. Resources Request Status
  7. Host Name (based on the connection string, as supplied by the calling application)
  8. Program Name (based on the connection string)
  9. Login Name
  10. Login Time
  11. Windows ProcessId for the process executing the query (on the host running the calling application)
  12. Last Wait type encountered by the query

How to find the T-SQL Query statement being executed from a batch?

The perplexing question here is that a batch or a stored procedure may have multiple statements – but, getting to the exact statement that is being executed at a given point needs a bit of manipulation.

Basically, the sys.dm_exec_requests DMV returns us information about each request that is currently being executed by SQL Server . The sys.dm_exec_sql_text() returns the text of the SQL batch that is identified by the specified sql_handle. The statement_start_offset  represents the number of bytes within the SQL batch indicated by the sql_handle at which the currently executing SQL Statement starts. Similarly, the statement_end_offset returns the number of bytes within the SQL batch where the currently executing SQL statement ends. Using these 2 values in a simple SUBSTRING function will return us the exact SQL Statement that is being executed by SQL Sever.

SELECT st.text AS CommandBeingExecuted,
          --Query being executed
          SUBSTRING(text,
                    -- starting value for substring
                    CASE WHEN ISNULL(statement_start_offset, 0) = 0 
                         THEN 1
                         ELSE statement_start_offset/2 + 1 END,
                    -- ending value for substring
                    CASE WHEN ISNULL(NULLIF(statement_end_offset, -1), 0) = 0 
                         THEN LEN(st.text)
                         ELSE statement_end_offset/2 
                    END - CASE WHEN ISNULL(statement_start_offset, 0) = 0 
                               THEN 1
                               ELSE statement_start_offset/2 END + 1
                   ) AS SQLStatementBeingExecuted,
       sr.wait_type AS WaitType,
       sr.wait_time AS WaitDuration,
       sr.command AS SQLCommandType,
       sr.status AS RequestStatus,
       ses.host_name AS HostName,
       ses.program_name AS ProgramName,
       ses.login_name AS LoginName,
       ses.login_time AS LoginTime,
       ses.host_process_id AS WindowsProcessId,
       sr.last_wait_type AS LastWaitType
FROM sys.dm_exec_requests AS sr
INNER JOIN sys.dm_exec_sessions AS ses ON sr.session_id = ses.session_id
CROSS APPLY sys.dm_exec_sql_text(sr.plan_handle) AS st;

If you have a similar query that you use, please share it in the comments area below.

References

Until we meet next time,
Be courteous. Drive responsibly.

Advertisements

One thought on “#0361 – SQL Server – Script to identify the currently running T-SQL query statement

  1. Chris Harrod

    SELECT r.cpu_time,
    r.logical_reads,
    r.session_id
    INTO #temp
    FROM sys.dm_exec_requests AS r

    WAITFOR delay ’00:00:01′

    SELECT Substring(h.text, ( r.statement_start_offset / 2 ) + 1,
    (( CASE r.statement_end_offset
    WHEN -1 THEN Datalength(h.text)
    ELSE Datalength(h.text)
    END ))) AS text,
    r.cpu_time – t.cpu_time AS CPUDiff,
    r.logical_reads – t.logical_reads AS ReadDiff,
    r.percent_complete,
    r.wait_type,
    r.wait_time,
    r.last_wait_type,
    r.wait_resource,
    r.command,
    r.database_id,
    r.blocking_session_id,
    r.granted_query_memory,
    r.session_id,
    r.reads,
    r.writes,
    r.row_count,
    s.[host_name],
    s.program_name,
    s.login_name
    FROM sys.dm_exec_sessions AS s
    INNER JOIN sys.dm_exec_requests AS r
    ON s.session_id = r.session_id
    AND s.last_request_start_time = r.start_time
    LEFT JOIN #temp AS t
    ON t.session_id = s.session_id
    CROSS apply sys.Dm_exec_sql_text(r.sql_handle) h
    WHERE is_user_process = 1
    ORDER BY 3 DESC

    DROP TABLE #temp

    Like

    Reply

Let me know what you think about this post by leaving your feedback here!

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s