rushi activated stored proc blocking

Hi There

I was wondering, i have experienced alot of stored procedure blocking where you have a stored procedure that get executed very frequently and is complex, when the sp has to recompile it is locked and cannot be executed by other processes.

How will this work with an activated stored procedure, our activated stored procedure will probably not be too complex but it may exec complex ones base on the message type.

Now lets say that a message is recieved by the activated sp he in turn executes a complex sp to process the message this causes the complex sp to recompile, during this time another activated sp is spawned by the queue with the same message type he execs the same complex sp that is busy recompiling, i presume that the second activated sp will be blocked until the sp has completed recompiling.

Is this correct, because on a very busy queue with very complex sp's being called by the activated stored procedure, the benefit of having multiple queue readers is negated by the blocking caused by the recompiling sp's called by the activated sp.

Basically no matter how many queue readers kick in to handle the messages they all wait for the sp to recompile from the first message.

I am hoping multiple spawned activated stored procedures somehow over come this Or work differently somehow.

Does anyone know

Thanx

The stored proc will be compiled only once. After that, all callers should be able to use the stored proc without blocking other callers.

If you need the stored procs to be modified frequently, that could result in constant recompilation as you have described above. In that case, you could use dynamic SQL and put the frequently changing code as strings in a table.

Hope that helps.

Hi Rushi

Perhaps i need to found out more about sp recompiles, we have a large sp about 1000 lines. It never changes, however it recompiles everytime it executes, not sure why. Thats why i was under the impression that very large complex sp's executed by the activated stored prcedure would blocked the additional spawned queue readers

What are you r thoughts on this Is it correct that sp's that have large amounts of dynamic sql will recompile frequently, i cannot put these strings in a table since they are dynamically created with variables passed to the sp.

Thank You