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