Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a situation where multiple client apps send messages via the Service Broker (utilizing stored procs). These messages are picked up by yet another client app and then processed. The way the messages are picked up is that the app issues the following SQL statement (pseudo code):

LOOP {
    WAITFOR (RECEIVE CONVERT(int, message_body) AS Message FROM SB_ReceiveQ)
    ProcessMessage
}

So basically the code just blocks until a message is received. This all works fine.

My question is about the implication of issuing a WAITFOR (RECEIVE... command which hangs on to some service broker based resource basically forever. Are there any performance issues associated with this pattern that I should know about?

For reference, this is SQL Server 2005.

share|improve this question

2 Answers 2

up vote 5 down vote accepted

You are blocking a worker in SQL Server and workers are limited, subject to max worker threads. This means there should not be thousands of requests blocked in WAITFOR(RECEIVE...) or you'll starve the server of workers.

But the first question that comes to mind is Why no leverage Service Broker Activation? This way you wouldn't be waiting all the time but only when activated because there are messages to receive.

share|improve this answer
    
So given that I will only have a single app with the WAITFOR... request, it shouldn't be an issue at all. –  AngryHacker Sep 16 '12 at 20:02

We've had 50+ machines doing this all day every day in SQL Server 2005. Been doing it for 3+ years without issue.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.