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.