Any DMV that can identify start time of currently running query within stored procedure

Question

Any DMV that can identify start time of currently running query within stored procedure

praveen_vejandla

SSCrazy

Points: 2069
More actions
April 21, 2014 at 8:33 am

#306616

Hi,
A stored procedure may have multiple lines of T-SQL code and one single line of SQL could be taking long time.
Is there any way to identify the starting time of execution for the currently running query within a stored procedure?
It seems we can get start time for the stored procedure but not for the specific currently running query within a long running stored procedure.
Any DMVs could be useful to capture this?
Thanks.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736162 More actions · Answer 1

This might help. Not sure if it's exactly what you need, but if it's running, this can determine some information: http://blogs.msdn.com/b/taylaf/archive/2010/01/25/determine-the-currently-executing-statement-in-a-long-running-sql-stored-procedure.aspx

If you have a trace or eventing set up, I believe you can capture when statements start and end in a procedure.

Sean Lange SSC Guru Points: 286590 More actions · Answer 2

praveen_vejandla (4/21/2014)
Hi,
A stored procedure may have multiple lines of T-SQL code and one single line of SQL could be taking long time.
Is there any way to identify the starting time of execution for the currently running query within a stored procedure?
It seems we can get start time for the stored procedure but not for the specific currently running query within a long running stored procedure.
Any DMVs could be useful to capture this?
Thanks.

That's because it executes as a single package. If you want to track start and stop of times of individual segments of a stored proc you would have to add auditing code to the proc itself.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 3

Steve has largely nailed it. Be cautious using extended events to capture all statements though. That can be a ton of information.

Now, if you're running SQL Server 2014, you can actually see not only what statement is actively running, but where it's at within the statement.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

praveen_vejandla SSCrazy Points: 2069 More actions · Answer 4

It seems apart from trace/extended events, there is no way to get the starting time for a specific SQL statement within stored procedure.

Thanks a lot for all the replies.