Performance impact of waiting on a service broker message

Question

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.

Answer 1

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.

Answer 2

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

Answer 3

The biggest issue I know of with an indefinite WAITFOR is log file expansion. This is described here. SQL Server hooks into its log when you start a transaction. All transactional events, such as UPDATEs to other tables, that happen after that transaction has started will be logged in the log “after” that. One can think of SQL Server writing to the log file in a circular way: when it reaches the end, it goes to the beginning and continues. However, once the log is filled with unreclaimable “active transactions”, it has to start expanding the log, if enabled.

Expanding the logfile is expensive. If the server needs to log things before it can respond to clients, those responses will have to wait for the log to expand. Additionally, if transactions last forever, the log will expand until it uses all available free space.

As long as your RECEIVE takes to run, it will prevent reclamation of the log. You can mitigate this by ensuring that your RECEIVE has a short enough timeout to prevent the log from growing indefinitely and handling the error condition of an event not coming in time for the timeout.