April 21, 2014 at 8:33 am
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.
April 21, 2014 at 8:45 am
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.
April 21, 2014 at 9:09 am
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/
April 21, 2014 at 10:29 am
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
April 21, 2014 at 12:23 pm
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.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply