Sql-server – Recommendations on how to organize Queues in Service Broker

queueservice-brokersql serversql-server-2008-r2

I have a service broker application that currently has 5 or 6 queues on two servers.

The general workflow is:

Server A

  • User populates some tables
  • User fires stored proc which puts a message into a header queue indicating there is work to be done
  • Broker reads from this header queue and creates a potentially large number of messages to send to Server B

Server B

  • Messages all go to 1 queue
  • In this queue, message is examined and unbundled, payload is run and a response is collected from another set of procs/tables that we interact with
  • Response message is created and sent back to Server A

Server A

  • Response messages go to a special queue and are used to update tables the user populated at the top
  • End dialog messages are read from the initiator queue and used to update another table that tracks the progress of a header record (i.e. 50 out of 100 messages processed)
  • When we detect that the header record has been entirely processed, a new message is sent to another queue that sends a notification email

I've had a recommendation to consolidate some of those queues on Server A and I'm not sure what the best practice is for this. Two of the queues will have a relatively high volume of message processing, and the email queue will be be pretty slow.

We did (I think) a good job of managing message types so theoretically we could put it all into one queue and the application would still function.

Is there a best practice on when to maintain separate queues and when to consolidate in a scenario like this?

Best Answer

This is an open question with no clear choice. YMMV so you have to test. Here is my opinion:

Having one queue to handle everything is a good choice if you want to be able to control the number of activated tasks, as there is no global max_queue_readers. Other than that, I don't see many advantages. One could argue that one single activated proc is easier to maintain, but I think you should use some sort of template/code generation when creating SSB activated process, since typically much is cookie-cutter code and little is specific to service business logic.

However I can see several potential problems with a single queue, and they're all the kind of problems that manifest only when things are already going bad (ie. under stress/load):

  • latency and processing serialization. With only one queue is possible to plateau the max_queue_readers at max and have messages which require little processing sit in the queue waiting their turn to be picked up. Separate queues allow low latency services to drain their queues fast, while high load queues churn at their own (slower) pace.
  • a spike of messages for any service affects all services. Same as above, but for spikes. A dump of a high number of messages (eg. some manual 'oop we need to reprocess this 1MM items') will cause all the other services on the same queue to wait until the spike is drained before they get access to their messages (is a bit more complex due to conversation group locking, but ultimately processing is FIFO).
  • avoidable visits into the ugly land of large queues. If big-queue problems occur on your RAM/IO/CPU/activate code at N messages, one-queue-for-all-services will reach N faster/more often than one-queue-per-service.

In general my recommendation is to have one-queue-for-each-service.

Related Question