Parallel Activation

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

—

create queue [ApplicationQueue]

create service [ApplicationService] on queue [ApplicationQueue] ([DEFAULT]);

go

— These are the activation queues that receive the event notifications

—

create queue [ActivatorQueue_1];

create queue [ActivatorQueue_2];

create queue [ActivatorQueue_3];

create queue [ActivatorQueue_4];

create service [ActivatorService_1] on queue [ActivatorQueue_1] ([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]);

create service [ActivatorService_2] on queue [ActivatorQueue_2] ([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]);

create service [ActivatorService_3] on queue [ActivatorQueue_3] ([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]);

create service [ActivatorService_4] on queue [ActivatorQueue_4] ([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]);

go

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!

create procedure [ApplicationActivationHelper_1]

as

begin

set nocount on;

declare @conversationHandle uniqueidentifier;

declare @messageTypeName sysname;

declare @notification xml;

declare @applicationMessage varbinary(max);

declare @error nvarchar(max);

begin transaction;

RECEIVE

@conversationHandle = conversation_handle,

@messageTypeName = message_type_name,

@notification = CAST(message_body as XML)

FROM [ActivatorQueue_1];

if (@messageTypeName = N‘http://schemas.microsoft.com/SQL/ServiceBroker/Error’)

begin

— This is an error received on the notification dialog

— log it to the event log

select @error = cast(@notification as nvarchar(max));

raiserror (N‘Notification activator [ActivatorQueue_1]: Received error %s’, 10, 1, @error) with log;

end conversation @conversationHandle;

end

else if (@messageTypeName = N‘http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog’)

begin

end conversation @conversationHandle;

end

— Done consumming the event notification, if any, continue

— with the actual application logic

—

WHILE (1=1)

begin

WAITFOR( RECEIVE

@conversationHandle = conversation_handle,

@messageTypeName = message_type_name,

@applicationMessage = message_body

FROM [ApplicationQueue]), TIMEOUT 1000;

if (@@rowcount = 0)

begin

— do NOT rollback here

break;

end

if (@messageTypeName = N‘http://schemas.microsoft.com/SQL/ServiceBroker/Error’)

begin

— This is an error received on the application dialog

— log it to the event log

select @error = cast(@notification as nvarchar(max));

raiserror (N‘Application: Received error %s’, 10, 1, @error) with log;

end conversation @conversationHandle;

end

else if (@messageTypeName = N‘http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog’)

begin

end conversation @conversationHandle;

end

else if (@messageTypeName = N‘DEFAULT’)

begin

— Here goes the real application logic.

— In our example, simply send back an echo

send on conversation @conversationHandle (@applicationMessage);

end

COMMIT;

BEGIN TRANSACTION;

end

commit;

end

go

alter queue [ActivatorQueue_1] with activation (

status = on,

max_queue_readers = 1,

procedure_name = [ApplicationActivationHelper_1],

execute as owner);

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

—

create event notification [ActivatorEvent_1]

on queue [ApplicationQueue]

for QUEUE_ACTIVATION

to service N‘ActivatorService_1’, N‘current database’;

create event notification [ActivatorEvent_2]

on queue [ApplicationQueue]

for QUEUE_ACTIVATION

to service N‘ActivatorService_2’, N‘current database’;

create event notification [ActivatorEvent_3]

on queue [ApplicationQueue]

for QUEUE_ACTIVATION

to service N‘ActivatorService_3’, N‘current database’;

create event notification [ActivatorEvent_4]

on queue [ApplicationQueue]

for QUEUE_ACTIVATION

to service 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:

create queue [Initiator];

create service [Initiator] on queue [Initiator];

go

declare @h uniqueidentifier;

begin dialog conversation @h

from service [Initiator]

to service N‘ApplicationService’, N‘current database’

with encryption = off;

send on conversation @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