Although conversation reuse will generally improve performance by reducing conversation management overhead while allowing batched RECEIVE processing, it might hinder performance when it comes to multithreading. The CREATE QUEUE command lets you specify the MAX_QUEUE_READERS option (shown in Listing 1), which controls how many instances of an activation stored procedure you can execute in parallel. In a busy system, performance would benefit from having multiple activation stored procedures service the queue. This is especially true if you have a system with multiple initiator queues sending messages to a single consolidated target. However, if your system uses a single initiator queue that sends messages on a single conversation, this option simply won’t work. Because Service Broker can’t process messages sent on the same conversation out of sequence, there’s no point in trying to multitask if only a single conversation exists.
If the message sequence isn’t critical to your solution, you can improve performance by changing conversation reuse code to send messages on multiple conversations, as shown in Listing 2. Ideally, the maximum number of conversations should match the target queue’s MAX_QUEUE_READER value. This final approach allows for activation stored procedure multithreading and batching RECEIVE calls for maximum performance, at the expense of message sequencing. ServiceBrokerMultithreadingDemo.sql demonstrates this approach.
The main benefit of the final approach is that you can control the level of parallelism used in processing the workload. On a busy system, you can increase the number of conversations that are created and match this increase in the queue MAX_QUEUE_READERS parameter. By doing so, you’ll allow Service Broker to run multiple activation stored procedures in parallel and achieve the highest throughput.