Is it possible to determine what part of a stored procedure is currently executing?

Question

Say I have a stored procedure like this (assume that the table schemas are fine):

CREATE PROCEDURE p_MyProc
AS

INSERT MyTable SELECT Col1 FROM Table1

INSERT MyTable SELECT Col2 FROM Table2

INSERT MyTable SELECT Col3 FROM Table3

Assuming that these end up being big inserts that can take several minutes each, is it possible to determine which one is currently running from within the context of a stored procedure?

I know how to find what stored procedure is running, I'm just looking to see if there's a way to get more granular without having to add PRINT or other tracing statements in there. Is there maybe a way to make the statements show up in a trace?

Answer 1

You can follow along with either a SQL Server Trace or Extended Events.

You can watch completed statements and/or batches, and that should give you the granularity you are looking for.

SP:StmtCompleted Event Class

Answer 2

I get this done through DMV's as you can see on the below script:

(it is part of a more detailed script, but I just extracted the part relevant to you in this question)

SELECT
----------------------------------------------------------------
-- get the query         - what command we are running inside the stored procedure 
-- and the parent query  - the the stored procedure 
-----------------------------------------------------------------
SUBSTRING (st.text 
                , (CASE WHEN er.statement_start_offset > DATALENGTH(st.text) 
                    THEN 0 ELSE er.statement_start_offset/2 END)+1
                , (CASE WHEN er.statement_end_offset <= 0 THEN DATALENGTH(st.text)
                    ELSE er.statement_end_offset 
                    END - CASE WHEN er.statement_start_offset > DATALENGTH(st.text) 
                        THEN 0 ELSE er.statement_start_offset/2 END)
                    + 1
                ) AS QUERY
    , st.text AS PARENT_QUERY
FROM sys.dm_exec_sessions es
    LEFT OUTER JOIN sys.dm_exec_connections ec ON es.session_id = ec.session_id
    LEFT OUTER JOIN sys.dm_exec_requests er ON es.session_id = er.session_id
    LEFT OUTER JOIN sys.server_principals sp ON es.security_id = sp.sid
    LEFT OUTER JOIN sys.dm_os_tasks ota ON es.session_id = ota.session_id
    LEFT OUTER JOIN sys.dm_os_threads oth ON ota.worker_address = oth.worker_address
    CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) AS st

You can see on the example below, it is doing an insert inside the stored procedure.