A brief guide to SQL Server Service Broker Kangmo Kim
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!
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].
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. 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. 
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.
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.  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.
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.
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.
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.
It is possible to create a dedicated queue on the target for the higher priority messages.
See [Arch-App] for the details.
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.
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.
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.
When you design your system using SSB, you need to consider following issues.
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. 
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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;
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.
 You can select sys.transmission_queue to see each message and its delivery status.
 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.
 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.
 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.1 Creating Message Types, Contracts, Queues, and Services
CREATE MESSAGE TYPE data_push_message VALIDATION = NONE;
CREATE CONTRACT data_push_contract
data_push_message SENT BY INITIATOR
CREATE PROCEDURE activated_procedure
< receive messages from queue >
< process all received messages >
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 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
DECLARE @handle UNIQUEIDENTIFIER;
DECLARE @payload VARCHAR(MAX);
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 messagesinto SSB queue
SEND ON CONVERSATION @handle MESSAGE TYPE data_push_message (@payload);
Start trying to send messages
Activation sproc is executed on message arrival
), TIMEOUT 5000;
Process all records in @receive_table
END CONVERSATION @conversation_handle;
Initiator ends conversation
[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)
SSB Info Center
This forum contains lots of useful Q&A and information
SSSB Team Blog
Remus Rusanu’s blog
Remus Rusanu is a former Microsoft employee,who is an expert on SSB
B.2 Technical Information
[Conv-Group] Conversation Groups
[Conv-Pri] Conversation Priority
[Ext-Act] External Activation
[Poison-Messages] Removing Poison Messages
[Monitor-SSB] Monitoring SSB with DMV, Performance Objects, and Trace Events
[Q-ONOFF] Starting and Stopping queues
[Handle-Poison] Handling Poison Messages
[MSDN-Trouble-Shoot] Troubleshooting Service Broker
[Opt-Steps] SSB T-SQL code optimization sample done step by step
[Dialog-Reuse] Reusing Conversations based on @@SPID
[Dialog-Pool] Reusing Dialog with a Dialog Pool
[Dialog-Recycle] Recycling Conversation
Use EndOfStream type instead of END CONVERSATION
[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
[SQL-Bulk-Copy] Inserting bulk data to the SQL Server
[Affinity-Mask] Affinity Mask Option on SQL Server
[Q-Monitor] Understanding Queue Monitor, which activates sprocs
Peeking SSB Transmission Queue
A simple sample replacing MSMQ to SB
[Act-Interval] Parallel Activation; Start all the configured max_queue_readers at once
[Custom-Priority] Processing Conversation With Priority Order
[Data-Push] Fast Data Push Tuning
[Table-Queue] Processing data queues in SQL Server with READPAST and UPDLOCK
SSB vs MSMQ