Find the current running statement text
-
I have several stored procedures that are performing poorly. When I check the job progress and find that it's still running, what's the best way to find the piece of SQL code (current running statement text) that is causing the "hang"?
I've started a trace on the procedure and been able to see the results from the duration column but it apparently is bad data that causes the issue and so it doesn't happen unless run from the job (being passed values). I need to know while the procedure is running what statement text is actually executing. Any ideas?
Thanks in advance.
-
Can you debug the proc?
-
I'm not sure I know what you're asking. I have the rights/ability to debug any proc, yes. What I need to be able to do is figure out (during runtime) what statement in a stored procedure is currently running during job execution.
THe key here is that I may not know what proc is currently running either since one procedure may call 3 or 4 others during its own execution. SO when you come in to the office, you're told a job is still running. OK, it shouldn't be, what piece of code in what SP is running? Answer that and you get a cigar!
-
Three options:
1. Debug the code
2. Put print statements after each section of the procedure to identify the section along with select getdate(). See eg. below
Print Section 1 done
select getdate()
The beggest differnce in 2 dates is your culprit.
3. Run profiler.
-
Are you tracing at the statement level within the procedure? That is, default level is just one entry for EXEC storedprocname... start at/end at. Detail allows the trace to show each statement within the proc as it executes...
This may still not show what's in the variables but you can combine it with adding print statements in your code, so that when it hangs you can kill it and check out what the last value was...
-
You can SQL Profiler to find out exactly where the problem is. You can also use blackbox to find out where the problem is. But blackbox tracking will slowdown the performance further. Once you have trapped the statements you can remove the black box trace.
Better method would be to use SQL Profiler to trace the statement, but you will not be able to get the parameter value.
-
John / Srinikrish,
Thank you both for your input. I am very familiar with Profiler and am able to see (basically) what statements execute. I'm interested though in where Profiler gets its information. I'm trying to build some scripts that can be run by developers that need to troubleshoot long running jobs when the DBA (me) isn't available. The scripts need to return object name, object type and statement text at the least. Trying to give everyone a lesson in the proper use of Profiler wouldn't be efficient.
Again thank you for your comments.
--Alex
-
dbcc inputbuffer() to capature the statements/sp they run.
-
He needs to know which line of the code in the sp is currently executing.. hence we suggested debugging.
-
The last statment from your jobs will be the sp that is still running.
Be remember that users can only see SPIDs they own if they are not 'sa'.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply