SQL Server Service Broker—Internal Activation

This post is part of a series on this blog that will explore SQL Server Service Broker, a native messaging and queueing technology built into the SQL Server Database Engine.

Previous posts:

See all posts in the series here.

In this installment, we discuss how to automate the processing of received messages through the use of a service program.

Service Programs

In the fourth installment of this series, we talked about sending and receiving messages in a Service Broker application. If you recall, when a message is received by the target service it’s placed in the target service queue to await processing. The processing of that message is performed by something called a service program. Often this service program takes the form of a stored procedure that receives messages from the queue and performs some logic on them.

Building on our previous example, we would create the following stored procedure as our service program.

[sourcecode language="sql"]
CREATE PROCEDURE usp_ProcessIRSQueue as
BEGIN
DECLARE @RecvReqDlgHandle UNIQUEIDENTIFIER;
DECLARE @RecvReqMsg xml;
DECLARE @RecvReqMsgName sysname;
WHILE (1=1)
BEGIN
BEGIN TRANSACTION;
WAITFOR (
RECEIVE TOP(1)
@RecvReqDlgHandle = conversation_handle,
@RecvReqMsg = cast(message_body as xml),
@RecvReqMsgName = message_type_name
FROM IRSQueue
), TIMEOUT 5000;
IF (@@ROWCOUNT = 0)
BEGIN
ROLLBACK TRANSACTION;
BREAK;
END
IF @RecvReqMsgName = N’//SBDemo/Taxes/TaxFormMessage’
BEGIN
DECLARE
@SSANumber varchar(9),
@LastName varchar(50),
@FirstName varchar(50),
@MiddleName varchar(50),
@BirthDate date,
@FilingStatus char(2),
@Wages Decimal(12,2),
@FederalIncomeTax Decimal(12,2),
@StateIncomeTax Decimal(12,2),
@SocialSecurityTax Decimal(12,2),
@Refund decimal(12,2)
select
@SSANumber = c.value(N'(./SSANumber)[1]’, N’varchar(9)’) ,
@LastName = c.value(N'(./LastName)[1]’, N’varchar(50)’) ,
@FirstName = c.value(N'(./FirstName)[1]’, N’varchar(50)’) ,
@MiddleName = c.value(N'(./MiddleName)[1]’, N’varchar(50)’) ,
@BirthDate = c.value(N'(./BirthDate)[1]’, N’date’) ,
@FilingStatus = c.value(N'(./FilingStatus)[1]’, N’char(2)’) ,
@Wages = c.value(N'(./Wages)[1]’, N’Decimal(12,2)’) ,
@FederalIncomeTax = c.value(N'(./FederalIncomeTax)[1]’, N’Decimal(12,2)’) ,
@StateIncomeTax = c.value(N'(./StateIncomeTax)[1]’, N’Decimal(12,2)’) ,
@SocialSecurityTax = c.value(N'(./SocialSecurityTax)[1]’, N’Decimal(12,2)’)
from @RecvReqMsg.nodes(‘Form1040EZ’) T(c)
EXEC CalculateRefund @FilingStatus, @Wages, @FederalIncomeTax, @Refund OUTPUT
DECLARE @ReplyMsg NVARCHAR(100);
IF @Refund < 10000
BEGIN
SELECT @ReplyMsg =
N’<Refund>’+cast(@Refund as varchar)+’</Refund>’;
SEND ON CONVERSATION @RecvReqDlgHandle
MESSAGE TYPE
[//SBDemo/Taxes/TreasuryCheckMessage]
(@ReplyMsg);
END
ELSE
BEGIN
SELECT @ReplyMsg =
N’<AuditNotice>Refund exceeds $10000: ‘+cast(@Refund as varchar)+’</AuditNotice>’;
SEND ON CONVERSATION @RecvReqDlgHandle
MESSAGE TYPE
[//SBDemo/Taxes/AuditNotificationMessage]
(@ReplyMsg);
END
END CONVERSATION @RecvReqDlgHandle;
END
ELSE IF @RecvReqMsgName =
N’http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog’
BEGIN
END CONVERSATION @RecvReqDlgHandle;
END
ELSE IF @RecvReqMsgName =
N’http://schemas.microsoft.com/SQL/ServiceBroker/Error’
BEGIN
END CONVERSATION @RecvReqDlgHandle;
END
COMMIT TRANSACTION;
END –WHILE 1=1
END
GO
[/sourcecode]

Additions to the previous code

If you look closely, you’ll notice that there are a couple key additions to this code.

The first is that we’ve wrapped our logic in a WHILE loop to run continuously. We’ve also wrapped our RECEIVE statement in a WAITFOR, with a timeout of 5 seconds. So SQL Server will wait for up to 5 seconds for a message to hit the queue. As long as our procedure receives a message from the queue, it will continue to loop and receive more messages. However, if no messages are received, the procedure breaks out of the loop and terminates.

Now, we could omit that WHILE loop and SQL Server will still process all of the messages in the queue. But, with the loop, Service Broker activation happens once, meaning the procedure is loaded into memory only once, and the slight overhead of activation is only incurred once. Without the loop, though, activation happens for every message waiting in the queue. And that’s why you’ll usually see that WHILE loop inside an a service program.

Now we have a procedure that will process the messages in our queue, but we still need to automate its execution. Depending on our needs, there are a couple ways to do this. The first way is to schedule this procedure to run on a regular basis, say as part of a SQL Agent job. This setup is useful if the processing of the messages isn’t time sensitive or if you’re looking to offload workload to non-peak hours.

Activation

The other way to automate this stored procedure is by attaching it to the queue itself so that it Service Broker directly executes the procedure in a process called activation.

With activation Service Broker starts an application whenever there is work to do (e.g. when messages are in the queue). When we enable activation for a queue, SQL Server spawns a queue monitor process to keep an eye on the queue and check every few seconds to see whether there are messages available for processing. There are 2 types of activation:

Internal activation – A stored procedure like the one above is attached to the queue to process messages automatically as they arrive.

External activation – An external program is notified that messages need to be processed.

Let’s focus on internal activation right now, since it’s far more widely used, in my experience.

To enable internal activation on my IRSQueue, I would use the following command:

[sourcecode language=”sql”]
ALTER QUEUE [dbo].[IRSQueue]
WITH
ACTIVATION (
STATUS=ON,
PROCEDURE_NAME=usp_ProcessIRSQueue,
EXECUTE AS SELF,
MAX_QUEUE_READERS=1
)
[/sourcecode]

Taking a closer look at the ACTIVATION clause, most of the parameters are pretty self-explanatory. However, MAX_QUEUE_READERS warrants some discussion. MAX_QUEUE_READERS specifies the number of instances of the activation stored procedure that can be started simultaneously. Setting it to one makes activation a single-threaded process.

Increasing MAX_QUEUE_READERS means that messages may be processed in parallel. As DBAs we’re all familiar with the possible performance benefits of parallelism. Best practice for the number queue readers in a high-throughput environment is 1-2 per CPU core, however you should scale appropriately for your workload.

MAX_QUEUE_READERS and message order

Parallelism in Service Broker also means that if there’s a message in the queue that requires additional processing, (or takes longer for some reason), other messages in the queue aren’t necessarily waiting for that message to finish before they can be processed.

But wait, didn’t I say in an earlier post that Service Broker guarantees messages wouldn’t be processed out of order? Wouldn’t parallel processing of messages break that rule? It depends. You see, technically Service Broker guarantees that messages in the same *conversation group* are processed in order.

A Brief Discussion on Conversation Groups

As you might imagine conversation groups are a way to group related conversations under a single identifier. Every conversation is part of a conversation group, whether you specify it or not. If a conversation group handle isn’t specified when a conversation is started, a new conversation group is created.

So, by default, each conversation is a member of a different conversation group and each conversation group contains a single conversation. When we issue a RECEIVE command, SQL Server places an exclusive lock on the next available (i.e. not locked by another queue reader) conversation group. So only one queue reader can read messages from a conversation group at a time, and therefore messages within the same conversation group cannot be processed out of order.

This highlights an important aspect of how you (yes you!) design your service broker application. If the order of messages is critical to your application, then you can choose to use a single queue reader and all messages will be processed in order, regardless of which conversation they’re part of. However, if you want to experience performance benefits of multiple queue readers processing messages in parallel, you need to ensure that related messages are sent as part of the same conversation or conversation group.

Conclusion

This has been an introduction to a big part of automating Service Broker, activation. We’ll discuss external activation in a future post. Until next time…

Original post by Colleen M. Morrow found here.