Invoking stored procedure takes a long time to start

[E11esar]

Hi there. I have a stored procedure which when invoked using EXEC in Sql Server Management Studio takes about 20 minutes to even start.

The EXEC command includes two input variables which hold XML strings of data, so I'm not sure if this would impact the startup at all?

Once the procedure does start, it finishes quite quickly but I can't see what is causing it to wait for so long to begin processing.

I have included debug statements within the stored procedure so I can see what step may be causing the bottle neck, but it doesn't even try to reach the first of these debug statements for a very long time so it just appears to hang for no reason, then it kicks off and all is good.

Any idea(s) on what causing the stored procedure to be inactive please? Is there some state in Sql Server that acts like a thread wait or the such?

Any help will be most welcome.

Thank you.

[ck9663]

There are a lot of reason why.

Put a SELECT 1 on the first line and then RETURN. If that happens immediately, then your SP runs fast, it's whatever inside it is making it slow.

Also, the debug feature that you might be waiting for to appear might be affected by I/O of the server, or the client or the network. So it has a lot of factors.

Good Luck!!!

~~ CK

[E11esar]

Yes I have done that with the stored procedure and it now takes about an hour to hit that first line.

Really is a strange one this.

Have tried Google-ing but so far no luck.

Thank you.

[ck9663]

How big is your XML?

~~ CK

[E11esar]

Each XML statement is about 10 lines long: probably around 300 charachters in length altogether.

An additional curiosity is I have replaced the series of SELECT 1 debugging statements with select getdate() statements so I can see what time each statement is hit and hence calculate how long specific portions of code take to process, and what I am getting back is getdate() values equal to when I started the stored procedure but these getdate()'s are not displaying for a good 50 minutes..!

It is as though the stored procedure is running but not sending a response for 50 minutes.

Very curious indeed.

[ck9663]

I cannot say for sure what's going on until I see your SP. But based solely on your results, it's more of an I/O problem.

Good Luck!!!

~~ CK