Sql-server – Performance impact of waiting on a service broker message

performanceservice-brokersql serversql-server-2005

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.

Best Answer

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.

Related Question