A brief guide to SQL Server Service Broker
                                                                                   Kangmo Kim

About This Document

SQL Server Service Broker(SSB) is message queuing infrastructure that is integrated into SQL Server. This document will briefly introduce overall architecture of SSB, important concepts, and optimization techniques. I am very new to SSB and Microsoft technology so please expect that some of information here may not be correct. Whenever I feel confident, I will remove this comment. A few sentences in this post are simply copied from SSB forums and blogs, but unfortunately I don't remember them.

I've got pretty much information in Remus Rusanu's blog. Thanks Remus!

Overall Architecture

The conceptual endpoint in SSB is a service.  A service is associated with a queue to store messages persistently. Two services should agree with a contract that defines possible message types that can be transferred between them. A dialog is a communication channel between two services. It is a persistent object in SSB and all SSB messages are sent on the dialog. You can use dialog and conversation in SSB interchangeably, as SSB does not support the monologue conversation, but dialog conversation. Messages sent through the dialogs in the same conversation group are serialized as shown in the green dialogs. By default, a dialog has its own conversation group and all messages in the dialog are guaranteed to be delivered in order as shown in the red dialog. In the target service, you can receive all messages in a specific dialog or dialog group.

For more information see [Arch-App] and [Arch].

Important Concepts

Message Delivery

Messages are sent through a dialog which has been established between two services. Messages in the dialog are guaranteed to be delivered in order without missing any of them. Once the initiator executes SEND statement to send a message, it is kept in SSB. Only after it COMMITs the transaction, SSB tries to send the message to the target service. Before getting delivered to the target service, the message stays in the transmission queue[1]. Once they arrive on the target queue, the target service sends ACKs to the initiating service. After receiving the ACKs, the initiating service removes all messages in the transmission queue. The initiating service retransmits all messages in the transmission queue until it gets ACKs from the target service.

On the target machine, you can receive multiple messages at once. After processing all the messages and you COMMIT the transaction, the messages are removed from the target queue. Once you receive a message in a conversation, all messages in the same conversation or conversation group are locked. [2]

Here the transaction and the lifetime of dialogs is not related to each other at all. You can commit transactions at any given time regardless of whether the dialog is began or not.

Activation

Once a message arrives at the target queue, the activation stored procedure is executed. After the activation is done, even though a new message arrives on the queue, SSB does not activate the stored procedure if it is being executed. [3] In case max_queue_readers parameter is specified on the target queue, SSB tries to execute the stored procedure in multiple threads. The queue monitor module of SSB monitors if the number of messages in the queue is increasing. In that case, it starts a new thread that executes the activation stored procedure.

However, executing a new stored procedure instance is done in five second’s interval, which is hard coded in SSB. This means that if you’re measuring activated procedures performance, you may have to consider that new procedure instances are launched at most one every five seconds. A good news is that there is a trick to control this interval, but the bad news is that it is not recommended to do so. See [Act-Interval] for details. About the queue monitor, see [Q-Monitor] for details.

 Conversation Groups

Messages are delivered in order in this dialog group. A conversation group can have one or more dialogs. There is a lock for the group, which is acquired each time SEND/RECEIVE statement is executed on the dialogs that are part of the group and released at the end of the transaction. This means that even though multiple transactions try to SEND/RECEIVE the messages through dialogs in the same conversation group, only one transaction can do it, blocking other transactions until the transaction that acquired the conversation group lock commits. The conversation group lock can be acquired explicitly by executing GET CONVERSATION GROUP statement with the conversation group handle as well.

See [Conv-Group] for the details.

Conversation Priority

There is nothing like ‘message priority’, as SSB is designed to send all messages in a conversation in order. However, you can send a specific conversation with higher priority.

 Using conversation priority

It is possible to adjust the priority for conversations with a specific contact, initiator service, and target service. The HONOR_BROKER_PRIORITY property needs to be turned on to use this feature.

 See [Conv-Pri] for the details.

Using a dedicated target queue for messages with high priority

It is possible to create a dedicated queue on the target for the higher priority messages.

See [Arch-App] for the details.

Implementing the priority by using SSB interface

The basic idea here is to send a request to the target to process a specific dialog with higher priority. This can be done by adding a special message type to the contract, that adjusts the priority of the specific dialog. See [Custom-Priority] for the details and sample codes.

Dialog Timeout

It is possible to have a timeout value for a dialog. In case the dialog is not closed by the END CONVERSATION command within the specified period, the broker ends the dialog with error. In this case, the initiator will receive a timeout message on the receiving queue for that dialog. You can specify the timeout in the BEGIN DIALOG statement. Alternatively, you can specify it with BEGIN CONVERSATION TIMER statement on a dialog that is already begun. For more information, See the page 28 of [Prog] for the details.

Automatic Poison Message Detection ; Queue States Becomes OFF

A potion message is a message containing information that the application cannot successfully process. Often, the poison messages were valid when the message was created, but later became impossible to process. In that case, the application will rollback the transaction that executed RECEIVE statement. SSB detects poison messages by seeing if the transaction is rolled back five times. In that case, SSB automatically sets the queue status to OFF. What this means is that the SSB is not able to send or receive using the queue. See [Poison-Messages] for the details.

Design Considerations

When you design your system using SSB, you need to consider following issues.

Ending Conversations

Both the initiator and target should end conversations. If you don’t end conversations, they will take up resources in your database.  You should end conversations on the initiator only after you received a SSB internal message type called EndDialog which is send by the target when it finishes processing all the messages in the dialog and execute END CONVERSATION. Otherwise SSB does not guarantee the delivery of messages in it. [4]

Deadlock Prevention

In case you need to lock multiple conversation groups in a transaction, you need to take the same order to lock the different conversation groups in all transactions, otherwise you will have a deadlock issues.

Error Processing

You need to process different kinds of errors while you send and receive messages. See TRY/CATCH blocks in the stored procedures in [Data-Push] for the details.

Handling Poison Messages

Try not to rollback the transaction that executes RECEIVE statement on a queue. If the transaction rolls back five times, the queue status will be changed to OFF automatically. In this case you need to write stored procedures that recovers from the poison messages. See [Handle-Poison] for the details.

Optimization Techniques

Beginning and Ending a conversation is a quite expensive task. You can either reuse or recycle the dialogs. On the target machine, you had better to use set-based queries instead of opening a cursor on the table variable that has received messages. If you are interested in a step by step guide that demonstrates SSB T-SQL code optimization, see [Opt-Steps]. It has fantastic code samples that increased the number of message delivery from 200 per second to 4000. Also keeping your transaction lifetime appropriate helps the system to have both nice throughput and latency. In this section I will list each of the optimization tips and references that has code samples.

Dialog pools ; Reusing dialogs

Instead of beginning and ending dialogs each time, we can implement dialog pools using T-SQL. You may have to have a dedicated thread that maintains dialogs. It will prepare them to return them quickly whenever a new begun dialog is needed. See [Dialog-Pool] to see a sample code for the dialog pool implementation. Also [Dialog-Reuse] has another code sample for reusing dialogs based on SQL server session ID, @@SPID.

Brief performance test results

Here are some experiences from the famous SSB expert, Remus Rusanu. This is what he wrote in the SSB forum.

·         The cost of setting up and tearing down a conversation for each message can influence the performance by ~4x.

·         The advantage on having multiple messages on the same conversation can speed up processing on the RECEIVE side by a factor of ~10x.

·         Two dialogs with 500 messages each will allocate far less resources than 1000 dialogs with a message each.

Recycling dialogs

As the cost of beginning and ending conversation is rather expensive, we can send a special message indicating the end of dialog. Of course, you need to create the specific message type for this. In the target machine, you can query the target queue which has the special message to find a dialog that is finished. After processing all messages in the dialog, you can just send the same special message indicating that all the messages are processed. In the initiating machine, once you receive the special message, you can put the dialog into a dialog pool without ending conversation. You may wonder how log you could keep a dialog without ending it. A dialog is a persistent object in SSB, so you can keep it begun as long as you wish.  See [Dialog-Recycle] for the details and code samples.

Use set-based queries

A strait forward example of receiving multiple messages from a queue at once would be receiving them into a table variable and open a cursor for it to process each of the rows in the order it arrived on the queue. Instead of fetching each record opening a cursor, you can just run a query on the table variable. This will improve the performance. See [Opt-Steps] for the code examples and actual performance enhancement from this tip.

Choose appropriate transaction lifetime

You may think that sending as many messages as possible in a transaction is good for the higher throughput. However this could result in several issues. First of all, long duration of conversation group locks could block other transactions that try to send messages through the conversation group. Secondly, you should expect a longer latency of the delivery, as SSB tries to send messages only after the transaction commits. Finally, the number of messages in the transmission queue will increase, as SSB will keep all the undelivered messages in the transmission queue. Processing an appropriate number of messages in a transaction is a crucial point for the highest performance.

 Fast way to check message count

You can check the message count looking at the row count of the underlying the b-tree that stores the messages.

See [Fast-Message-Count] for the details.

Administration

Monitoring SSB

SSB provides various DMV(Dynamic Management View)s, Performance Objects, and Trace Events. For example, sys.dm_broker_connections DMV lists all service broker network connections.

See [Monitor-SSB] for the details.

Stopping/Starting SSB Queues

You can execute ALTER QUEUE statement to start or stop SSB queues. When a queue is started, the queue is available to send and receive messages. When a queue is stopped, Service Broker does not deliver new messages to the queue, and does not allow applications to receive messages from the queue.

 

ALTER QUEUE [queue name] WITH STATUS = OFF;

ALTER QUEUE [queue name] WITH STATUS = ON;

 

See the [Q-ONOFF] for the details.

Reseting SSB

By executing the following statement, you can remove all existing conversations, all conversation groups and all messages. It does not change in any way the services, contracts, message types, security setup, grants etc.

ALTER DATABASE [database name] SET NEW_BROKER WITH ROLLBACK IMMEDIATE;

Troubleshooting

You can find the troubleshooting techniques for activation stored procedures; routing and message delivery. See [MSDN-Trouble-Shoot] and [Internal-Trouble-Shoot] for the details.



[1] You can select sys.transmission_queue to see each message and its delivery status.

[2] Actually this is not done by locking each of the messages or conversations, but locking the conversation group once you execute RECEIVE statement in a transaction. All other transactions that tries to read any message from the same conversation group will be blocked.

[3] SSB is not designed to activate the stored procedure for each message that arrives on the queue. The stored procedure should be designed to dequeue as many messages as possible once it is executed.

[4] Even you end the conversation right after sending messages, your application may look working fine for a while. However you may end up with finding some missing messages in the future.

 

A.    Overall Execution of SSB T-SQL Codes

A.1 Creating Message Types, Contracts, Queues, and Services

Description

Initiator

Target

Create

messages

types

CREATE MESSAGE TYPE data_push_message VALIDATION = NONE;

 

Same

Create

contract

CREATE CONTRACT data_push_contract

 (

     data_push_message SENT BY INITIATOR

 );

Same

Create

activation

procedure

 

CREATE PROCEDURE activated_procedure

AS BEGIN

WHILE(1=1)

BEGIN

     BEGIN TRANSACTION;

     < receive messages from queue >

     < process all received messages >

     COMMIT

END

END

Create

queue

 

CREATE QUEUE initiator_queue;

CREATE QUEUE target_queue

    WITH ACTIVATION (

    STATUS = ON,

    MAX_QUEUE_READERS = 1,

    PROCEDURE_NAME = activated_procedure,

    EXECUTE AS OWNER)

Create

service

CREATE SERVICE initiator_service ON QUEUE initiator_queue (data_push_contract);

CREATE SERVICE target_service ON QUEUE target_queue (data_push_contract);

A.2 Sending and Receiving Messages

Begin

transaction

BEGIN TRANSACTION;

 

Variable

declaration

DECLARE @handle UNIQUEIDENTIFIER;

DECLARE @payload VARCHAR(MAX);

 

Begin

dialog

BEGIN DIALOG CONVERSATION @handle

FROM SERVICE initiator_service

 TO SERVICE 'target_service'

 ON CONTRACT data_push_contract

 WITH ENCRYPTION = ON;

Dialog in the target is automatically created.

Put messages
into SSB queue

SEND ON CONVERSATION @handle MESSAGE TYPE data_push_message (@payload);

……

 

Start trying to send messages

COMMIT

 

Activation sproc is executed on message arrival

 

BEGIN TRANSACTION;

WAITFOR (

    RECEIVE TOP(@max_messages_per_receive)

    queuing_order,         

    conversation_handle,

    message_type_name,

    message_body

    FROM target_queue

    INTO @receive_table

), TIMEOUT 5000;

Process all records in @receive_table

END CONVERSATION @conversation_handle;

COMMIT;

Initiator ends conversation

BEGIN TRANSACTION;

END CONVERSATION @conversation_handle;

COMMIT;

 

 

 

B.    References

B.1 Learning

General Information

Introduction

http://msdn.microsoft.com/en-us/library/ms345108.aspx

 

[Arch] Architecture

http://msdn.microsoft.com/en-us/library/ms166125.aspx

 

[Arch-App] Architecting Service Broker Applications

This article moves up a level from the "how" to discuss "why" Service Broker should be used, and what decisions have to be made to design and build a Service Broker application successfully. (18 printed pages)

http://msdn.microsoft.com/en-us/library/aa964144.aspx

 

Tutorials

http://msdn.microsoft.com/en-us/library/bb839489(SQL.90).aspx

 

SSB Info Center                                       

http://msdn.microsoft.com/en-us/library/ms166043(SQL.90).aspx

Forums, Blogs

SSB Forum

This forum contains lots of useful Q&A and information

http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=91&SiteID=1

 

SSSB Team Blog

http://blogs.msdn.com/sql_service_broker/default.aspx

 

Remus Rusanu’s blog

Remus Rusanu is a former Microsoft employee,who is an expert on SSB

http://rusanu.com/blog/

B.2 Technical Information

Concepts

[Conv-Group] Conversation Groups

http://msdn.microsoft.com/en-us/library/ms166131.aspx

 

[Conv-Pri] Conversation Priority

http://msdn.microsoft.com/en-us/library/bb934439.aspx

 

[Ext-Act] External Activation

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1462413&SiteID=1

 

[Poison-Messages] Removing Poison Messages

http://msdn.microsoft.com/en-us/library/ms166137.aspx

Administration

[Monitor-SSB] Monitoring SSB with DMV, Performance Objects, and Trace Events

http://msdn.microsoft.com/en-us/library/ms166069(SQL.90).aspx

 

[Q-ONOFF] Starting and Stopping queues

ttp://msdn.microsoft.com/en-us/library/ms166121(SQL.90).aspx

 

[Handle-Poison] Handling Poison Messages

http://msdn.microsoft.com/en-us/library/ms171592.aspx

Troubleshooting

[MSDN-Trouble-Shoot] Troubleshooting Service Broker

http://msdn.microsoft.com/en-us/library/ms166133(SQL.90).aspx

Optimization

[Opt-Steps] SSB T-SQL code optimization sample done step by step

http://rusanu.com/2006/10/16/writing-service-broker-procedures/

 

[Dialog-Reuse] Reusing Conversations based on @@SPID

http://rusanu.com/2007/04/25/reusing-conversations/

 

[Dialog-Pool] Reusing Dialog with a Dialog Pool

http://blogs.msdn.com/sql_service_broker/archive/2008/07/25/reusing-dialogs-with-a-dialog-pool.aspx

 

[Dialog-Recycle] Recycling Conversation

Use EndOfStream type instead of END CONVERSATION

http://rusanu.com/2007/05/03/recycling-conversations/

 

[Fast-Message-Count] Fast way to check message count

A way to look at the row count of the underlying the b-tree that stores the messages

http://rusanu.com/2006/11/09/fast-way-to-check-message-count/

 

[SQL-Bulk-Copy] Inserting bulk data to the SQL Server

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy.aspx

 

[Affinity-Mask] Affinity Mask Option on SQL Server

http://msdn.microsoft.com/en-us/library/ms187104.aspx

SSB Internals

[Q-Monitor] Understanding Queue Monitor, which activates sprocs

http://rusanu.com/2008/08/03/understanding-queue-monitors/

Code Samples

Peeking SSB Transmission Queue

http://msdn.microsoft.com/en-us/library/bb522922.aspx

 

A simple sample replacing MSMQ to SB

http://blogs.msdn.com/rushidesai/archive/2005/04/28/746811.aspx

 

[Act-Interval] Parallel Activation; Start all the configured max_queue_readers at once

http://rusanu.com/2006/10/29/parallel-activation/

 

[Custom-Priority] Processing Conversation With Priority Order

http://rusanu.com/2006/03/28/processing-conversation-with-priority-order/

 

[Data-Push] Fast Data Push Tuning

http://blogs.msdn.com/sql_service_broker/archive/2008/08/12/fast-data-push-tuning.aspx

 

[Table-Queue] Processing data queues in SQL Server with READPAST and UPDLOCK

http://www.mssqltips.com/tip.asp?tip=1257

SSB vs MSMQ

SSB vs MSMQ

http://www.devx.com/dbzone/Article/34110