0

I've built a data collection framework around service broker. There are several procs that fill the queue with various jobs. Then a listener (activated procedure) that takes the jobs, decides what needs to be done with that item, and hands it off to the correct collection proc.

The activation queue has a MAX_QUEUE_READERS of 10, but almost never reaches that limit. Instead it will take far longer to process with just 1 or 2 activated tasks as seen from dm_broker_activated_tasks.

How can I incentivize or even force the higher number of workers?

EDIT: THIS MS doc says it only checks for activation every 5 sec.

Does that mean if my tasks take less that 5 seconds I have no way to parallelize them through service broker?

meteorainer
  • 913
  • 8
  • 21

1 Answers1

2

Service Broker has a specific concept for parallelism, namely the conversation group. Only messages from different groups can be processed in parallel. How this manifests is that a RECEIVE will lock the conversation group for the dequeued message and no other RECEIVE can dequeue messages from the same conversation group.

So even if you do have more messages in your queue, if they belong to the same conversation group then SQL Server cannot activate more parallel readers.

Even if you don't manage conversation groups explicitly (almost nobody does), they are managed implicitly by the fact that a conversation handle is also a group. Basically, every time you issue a single BEGIN DIALOG followed by several SEND on the same handle, they will not be processable in parallel. If you issue separate BEGIN DIALOG for each SEND they are processable in parallel, but you loose the order guarantee.

Remus Rusanu
  • 288,378
  • 40
  • 442
  • 569
  • I'm using a cursor to create a conversation handle and send the for each ingestion task, so that's not it. Good thought though. – meteorainer Dec 04 '17 at 14:54