Managing Service Broker Conversations

SQL Server 2005’s Service Broker uses a new concept of a dialog conversation. Service Broker conversations are conceptually similar to T-SQL connections, but there are a few important differences that could affect performance, and even have the potential for data loss. I’ll discuss a few common pitfalls related to Service Broker conversation management and provide some simple solutions and workarounds.

Before you start sending Service Broker messages, you have to start a conversation between Service Broker services. You can do so by using the new BEGIN DIALOG syntax. Once you’re done sending Service Broker messages, you can end the conversation. This is similar to what you do with T-SQL connections. For example, you could connect to the server, execute a stored procedure, and then disconnect from the server. However, if you use Service Broker in this way, not only will performance suffer because there’s no connection pooling, but you’ll also be exposing yourself to potential data loss.

The way Service Broker is implemented, sent messages first land on the sys.transmission_queue. This system queue stores messages before they’re routed to the target queue. If Service Broker can’t route the message because of invalid XML in the message, incorrect routing configuration, or a disabled queue, the messages will remain in the transmission queue until the problem is resolved. Under some conditions (e.g., an XML validation problem) the messages will be dropped once the conversation is closed. So if you send messages and end the conversation before they’re processed, you could lose them. ServiceBrokerDataLossDemo.sql demonstrates this scenario. (You can download ServiceBrokerDataLossDemo.sql, and the other executable files, by clicking the 103131.zip hotlink under Download the Code at the top of the page.)

Here’s one simple way to prevent data loss without having to write a lot of code to check if your messages have arrived. The trick is to never end a conversation on the initiator end first. Instead, define an additional user message type that’s used to communicate that the sender “wants” to end the conversation, as shown in the following command:

CREATE MESSAGE TYPE [END_CONVERSATION]VALIDATION = EMPTY;

Although the initiator signals the conversation end, the target queue is the one to actually end it. The initiator queue should then complete the process by also ending the conversation. Because Service Broker is a FIFO queue, the “end conversation” message is processed after the data messages. ServiceBrokerEndDialogDemo.sql illustrates this approach.

This approach will prevent data loss, but it’s still not doing much for performance. If you begin a conversation, send a single message, and then end the conversation, you’ll be creating significant conversation management overhead. You’ll also never be able to receive more than a single message at a time. This is because the RECEIVE statement can only get messages from a single conversation. That means you wouldn’t be able to use the RECEIVE TOP(X) syntax to get multiple messages and process them as a batch, a strategy that can significantly improve performance in a busy system. ServiceBrokerReceiveDemo.sql shows the interaction between conversations and RECEIVE statement batching.

The best way to get around this problem is to reuse conversations. Instead of sending a single message, you could send several messages before closing the conversation. Because Service Broker is most useful in real-time and near real-time scenarios, you typically don’t have the luxury of processing multiple messages inside a loop. It’s more likely that your send code sits inside a trigger that fires every time a record is inserted or modified. This presents the problem of persisting the conversation handle GUID between executions. One solution would be to store the GUID in a table. The problem with this approach is that at the time of processing the next event, the conversation might have been closed, so the handle would be invalid. An alternative approach is to interrogate the sys.conversation_endpoints system view to find a valid open conversation, as shown in the following code:

SELECT TOP(1)@dialog_handle = [conversation_handle]FROM sys.conversation_endpoints with(nolock)WHERE [state] = 'co'AND far_service = 'TargetService'ORDER BY lifetime DESC;

A new conversation is started only if a valid conversation isn’t found. The advantage of using this approach is that you don’t have to worry about storing GUIDs; SQL Server will do that for you. Also, the conversation handle you obtain will be valid even if the one used in the previous run no longer is. ServiceBrokerConversationReuseDemo.sql demonstrates this approach.

Although conversation reuse will generally improve performance by reducing conversation management overhead while allowing batched RECEIVE processing, it might hinder performance when it comes to multithreading. The CREATE QUEUE command lets you specify the MAX_QUEUE_READERS option (shown in Listing 1), which controls how many instances of an activation stored procedure you can execute in parallel. In a busy system, performance would benefit from having multiple activation stored procedures service the queue. This is especially true if you have a system with multiple initiator queues sending messages to a single consolidated target. However, if your system uses a single initiator queue that sends messages on a single conversation, this option simply won’t work. Because Service Broker can’t process messages sent on the same conversation out of sequence, there’s no point in trying to multitask if only a single conversation exists.

If the message sequence isn’t critical to your solution, you can improve performance by changing conversation reuse code to send messages on multiple conversations, as shown in Listing 2. Ideally, the maximum number of conversations should match the target queue’s MAX_QUEUE_READER value. This final approach allows for activation stored procedure multithreading and batching RECEIVE calls for maximum performance, at the expense of message sequencing. ServiceBrokerMultithreadingDemo.sql demonstrates this approach.

The main benefit of the final approach is that you can control the level of parallelism used in processing the workload. On a busy system, you can increase the number of conversations that are created and match this increase in the queue MAX_QUEUE_READERS parameter. By doing so, you’ll allow Service Broker to run multiple activation stored procedures in parallel and achieve the highest throughput.

Listing 1: MAX_QUEUE_READERS Option

CREATE QUEUE  WITH STATUS = ON, ACTIVATION(STATUS = ON, PROCEDURE_NAME = , MAX_QUEUE_READERS = max_readers_number, EXECUTE AS SELF);

Listing 2: Managing Multiple Conversations’ Code

--@current_thread is a random number between 1 and max. thread numberSELECT TOP(@current_thread)@dialog_handle = [conversation_handle]FROM sys.conversation_endpoints with(nolock)WHERE [state] = 'co'AND far_service = 'TargetService'ORDER BY lifetime DESC;IF @@rowcount