Stored procedure won't complete

irb56
irb56 used Ask the Experts™
on
Hi experts,

I have an urgent problem that I hope you can help me with. I have a stored procedure (let's call it stored_proc_A) that runs twice a week via a SSIS package run through a scheduled SQL Server Agent job. For over a year, this has run fine. Normally the SQL Server Agent takes less than 2 minutes to run the SSIS package that includes stored_proc_A. This morning, the SQL Server Agent job ran for about an hour and a half before I eventually cancelled it. I tried restarting SQL Server and running the SSIS package manually in BIDS, but the same thing is happening. The step that runs stored_proc_A is still yellow after more than half an hour and is still running as I write this.

The stored_proc_A is a vital component of the SSIS package that produces a report, which must go out today. I've tried a few things to work out what's going on, including checking if the SPID is blocked (apparently not), whether there are any open transactions (apparently not), etc. I'm not experienced with this kind of problem so any assistance you can provide to help me get to the bottom of what's going on will be much appreciated.

As a point of note, I did some work related to stored_proc_A yesterday, which may be relevant (although I don't understand why). Within stored_proc_A, it calls another stored procedure called stored_proc_B. I created a table yesterday and populated it with a few records (about 50) that need to be excluded from the report in question. I then amended stored_proc_B (which excludes problem records from the report) so that in addition to its normal exclusion rules it also picks up records on this new table and excludes them from the main report. During testing yesterday, I inserted these problem records manually into the main report table and then ran stored_proc_B (in SSMS) successfully to verify that it correctly extracts these records on the exception report, which it did.

Nothing has changed in the stored_proc_A procedure, so I'm stumped. All I've been able to deduce so far is that the SPID associated with stored_proc_A is not blocked and continues to consume CPU and IO resource. When I filter the default SQL Profiler template on the SPID, I get no events at all apart from the iintial ExistingConnection details. The SQL Server Agent job history reports no error or warning messages and its the same in the Windows Application event log. It's as if the stored procedure is stuck in a loop doing nothing. Can someone please help?

Many thanks in advance.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
Profiler
Default templates only shows event RPC:Completed (end off execution stored procedure).
Therefore you must select other events in the Stored Procedure set
  RPC:started start of the stored proc
  SP:StmtStarting and SP:StmtCompleted with these you can monitor the individual statements off that procedure

So the 'starting' let you see what is the event that is busy and with monitoring the individual statements  you get to the specific problem.

Author

Commented:
Thanks very much jogos! By including SP_StmtStarting and SP_StmtCompleted in the profiler trace I can see that the stored procedure is very much active. Furthermore, I can see the source of the performance problem. There is a variable set statement that I recognise from a function that I changed as part of the work I did yesterday. This function is being called many times and this particular statement is taking 40ms each time (in contrast all the other statements are reporting 0ms duration). From this it seems clear that my change to a function yesterday has killed the stored proc performance. I guess the stored proc will finish eventually (hopefully before the end of the day!) but in the meantime I'll do some optimization work to correct this.

Many thanks for such a prompt reply, which is very much appreciated!  :-)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial