Parallel Activation

October 29th, 2006

A frequent question from customers is ‘How can I change the activation timing?’. The short answer is that you cannot, this is hard coded in the SQL Server engine and cannot be customized. But the reason why most people want the timing changed is to start all the configured max_queue_readers at once. I will show you an unorthodox trick that can be used to achieve this result.

Normally the activation mechanism monitors the queues and the RECEIVEs occurring and decides when is appropriate to launch a new instance of the activated procedure. However there is another, less known, side of activation: the QUEUE_ACTIVATION event notification. Those of you familiar with the External Activation sample for sure have learned about these events. This works similarly to the classical activated procedure, but instead of launching an instance of the activated procedure, a notification is sent on the subscribed service. The key difference is that there is no restrictions on how many different notification subscriptions can be created for the same QUEUE_ACTIVATION event! And when there is the time to activate, all the subscribers are notified. These notifications are ultimately ordinary Service Broker messages sent to the subscribed service. These subscribed services are running on queues that, obviously, can have attached procedures to be activated. See where I am going? You can use the subscribed service’s queue activation to launch a separate procedure per subscribed service for each original queue activation notification. So if you create 5 QUEUE_ACTIVATION subscriptions from 5 separate services, you will launch 5 procedures (nearly) simultaneously!

Here is a code example showing this:

— This is the real application queue that receives the application messages

Now create the activated procedures. Note that they will be activated on the [ActivatorQeueu_… queues, but they have to RECEIVE from the original [ApplicationQueue]. But they cannot simply ignore the [ActivatorQueue_…], they have to RECEIVE the message that activated them, because otherwise the activation on [ActivatorQueue_…] will simply stop activating them!

You’re gonna have to repeat this 3 more times, for each [ActivatorQueue_…]. I highlighted the places where you need to change the _1 into _2, _3, _4 etc. BTW, what happens if we change the number of max_queue_readers here? We’ll get a ‘batch’ behavior, where whenever the [ApplicationQueue] monitoring believes there is need to activate one more reader, a whole set of readers will be activated, up to the max_queue_readers * number of subscribed services.

Now create the 4 event notification subscriptions:

— Now create the event notifications, one for each activator service

—

createeventnotification [ActivatorEvent_1]

onqueue [ApplicationQueue]

for QUEUE_ACTIVATION

toservice N‘ActivatorService_1’, N‘current database’;

createeventnotification [ActivatorEvent_2]

onqueue [ApplicationQueue]

for QUEUE_ACTIVATION

toservice N‘ActivatorService_2’, N‘current database’;

createeventnotification [ActivatorEvent_3]

onqueue [ApplicationQueue]

for QUEUE_ACTIVATION

toservice N‘ActivatorService_3’, N‘current database’;

createeventnotification [ActivatorEvent_4]

onqueue [ApplicationQueue]

for QUEUE_ACTIVATION

toservice N‘ActivatorService_4’, N‘current database’;

go

Notice how the 4 subscriptions are for the same event (QUEUE_ACTIVATION on [ApplicationQueue]), but the subscribed service is different for each one.

Last step we need to do is verify how all fits together. Attach the Profiler to the server and monitor the Broker/Broker:Activation event to see how the procedures are activated/shut down. Then we can send one message to the ApplicationService and see how 4 activated procedures will race at once to grab this message. Of course, only one will succeed in our example, the other 3 will time out and deactivate themselves:

createqueue [Initiator];

createservice [Initiator] onqueue [Initiator];

go

declare @h uniqueidentifier;

begindialogconversation @h

fromservice [Initiator]

toservice N‘ApplicationService’, N‘current database’

with encryption =off;

sendonconversation @h (N‘Test’);

The question arises now: is this for real? Should a production system use this trick? I’d recommend against. The interaction between the QUEUE_ACTIVATION event on the [ApplicationQueue] and the internal activation on the [ActivatorQueue_…] queues is complex and very easy to get awry. The maintenance of the almost identical [ApplicationActivationHelper_…] procedures is difficult, any change has to me replicated to all the procedures. Changing the number of activated procedures is difficult and cumbersome; it involves creating and dropping new event notification, as well as the entire necessary infrastructure to each subscribed service: queue, service and procedure. However, if you really believe you need to activate multiple procedures at once, this trick can help you. For sure it can be impressive for demonstrations J