Sql server 2008 service broker millions of queues

Question

I am working on wireframe for new feature in our application. One of the requirements is each user has a list of things to process.

I am planning to use SQL Server 2008 service broker to send notifications of to-do items to users.

But with the way the broker queue is working I can't isolate messages for users in a single queue. I can only see after a message is retrieved. However, I only want it to retrieve a message for the user that is calling it.

So if I want to use broker service I would need a separate queue for each user.

Then I would end up having millions of queues in broker service.

Am I missing any feature of broker service that can allow me to have one queue but retrieve messages with a filter?

Can SQL Server 2008 broker service handle millions of queues and is it ok to do so?

Answer 1

Messaging applications in general are event driven, ie. when a message comes it activates a processing procedure that handles the message. Service Broker implements this through the activation mechanism attached to queues. In this philosophy there is no need to filter messages, as the processing always processes 'the next message' in the queue, and the queue is, ideally, always empty. As a result the RECEIVE verb does not offer any filter. The WHERE clauses of RECEIVE are restricted to conversations and conversation groups and this is as a means to help application leverage the conversation group locking concept, not to filter our certain messages.

Although queues are nothing more than ordinary (internal) tables disguised under a fancy name they carry some baggage implied that would make having millions a bad idea:

• Each queue must have at least a service associated with it. Services are a bit more heavyweight in runtime cost, since they are associated with message routing and thus they consume start up time (to scan the db and create the tempdb hidden instance routing table), tempdb space (the said routing table) and runtime memory (cached memory structures associated with routing). They would also induce some CPU overhead (for the routing algorithm to decide between millions of candidates).
• Queue can become much heavier 'on their own' if activation is associated with them. Each queue will have a runtime object, the Queue Monitor, that will consume memory and CPU, see http://rusanu.com/2008/08/03/understanding-queue-monitors
• You will need at least one conversation to reach a queue, and live, active, conversations can occur some overhead.
• The queue object is associated with the RECEIVE verb and, more importantly, with the WAITFOR(RECEIVE). WAITFOR blocks a worker thread on the server and you will run out of sp_configure 'max worker threads' long, long before you place a WAITFOR on each queue.

Also queue/services are a bit more 'active' from a configuration/monitoring requirement point of view than an ordinary table. There are two problems I have in mind when I say this: poison message detection can trigger and disable your queue, you'll have a hard time keeping an eye on a million queues (it can and should be automated, but still). Second is the problem than can occur if, for whatever reason, messages are delayed through the transmission queue: when transmission resumes having millions of conversations woken up will behave quite bad (milleage may vary, depends a lot on what SQL version/SP level you are as some issue were fixed post SQL 2K5 SP3).

Service Broker was designed primarily in mind with the problems of distributed applications (ie. to address the issues unaddressed by DCM/COM+, Corba, Remoting and the like). But is it often deployed locally, entirely contained within one single SQL server instance or even database, to leverage the queuing behavior and the activation mechanism. If the sole reason you're looking at SSB is to leverage queues, then I'd say is a 50/50 split between using thew SSB queues vs. building your own user table backed queues, with a lean toward building your own if you have the expertise (ie. you know how to solve the many deadlock issues that come with user table backed queues). If you're also leveraging Activation, then SSB has a lot more appeal, and if you are actually doing remote messaging then is clearly the best option.

In your case, since I hope I made it clear why having million of queues is a bad idea. where do your messages originate (local vs. remote), and why are you considering Service Broker to start with? W/o knowing all the details, I'd say that the best alternative is to have one queue, an activated procedure that processes all messages as they arrive and deposits the relevant information for each user in a normal table that can be properly filtered by each user for its own data.

Disclosure: I am a former member of the Service Broker team and maintain the SSB blog