Using SQL 2008 ServiceBroker for high-volume threadsafe FIFO queue

Question

I'm just starting to evaluate ServiceBroker to determine if it can perform as a reliable queue in a very specific context. Here is the scenario:

(1) need to pre-calculate a large (several million) population of computationally expensive values and store in a queue.

(2) multiple processes will attempt to read/dequeue these values at run time on an as-needed basis. could be several hundred + reads per second.

(3) a monitor process will occasionally poll the queue and determine if the population minimum threshold has been reached, and will then re-populate the queue.

Due to some infrastructure/cost constraints, an industrial strength Queue (websphere) might not be an option. What I have seen thus far of Service Broker is not encouraging because it seems to be isolated to a "conversation" with 2 endpoints and in my scenario, my reads happen completely independent of my writes. Does anyone have any insight as to whether this is possible with SQL Service Broker?

Answer 1

Although Service Broker has not been designed for such scenarios, I think with a little tweaking it could work in you case.

One approach would be to pre-create a pool of conversations and then have the calculating process round-robin between theses conversations when storing values. Since receiving from a queue takes a lock on the conversation, the number of conversations essentially sets an upper bound on how many processes may dequeue values concurrently. I'm not sure about that, but you may need some logic on the receiver side to tell explicitly what conversation to receive from (in order to achieve better load balancing than default receive behavior would get).

If perf is not a concern then you may even drop the conversation pool idea and send each message on a separate dialog, which would make the implementation way simpler at the cost of significant perf hit.

All the above is said assuming the values may be dequeued in a random order, otherwise you need to guarantee the receive ordering by using a single conversation.