Tuesday, May 20, 2008

Three Things You Should Do When Using SQL Service Broker

There's three things that I've learned (the hard way) recently while working with the SQL Server Broker.  Not doing the first two of these things will really bite you.   Really bite you.  I mean, like, SQL Server will punch you in the neck, steal your wife and eat your babies, bite you...

The symptoms of not doing these things are:

  • Saturated CPU on the SQL Server
  • Out of control tempdb growth
  • Calls from angry customers

Back in January, I put together a post that used a "roll your own" approach to cache invalidation, and in that example I used the SQL Server Service Broker in order to notify the application that there was data that changed.  The sample issues and solutions  that I'm using below are from that post, so refer there to get the whole sample (I've updated that post to make sure I don't get anyone punched in the neck).

SQL Broker is designed to be the new "middleware", and is primarily setup to send messages between SQL Server Brokers.  All messages are sent in the context of a conversation.  The concept of conversation ensures that messages are sent in order, only received once, and are delivered reliably.

All of these symptoms above have to do with not knowing what the hell I'm doing using conversations properly, which leads me to believe there's quite a bit of overhead having to do with the management of conversations.

So, have a look below.  Definitely do things #1 and #2, and see if you can pull off #3.

1.  Always End the Conversation

This one seems to be pretty often overlooked.  In my example from back in January, I was using a one sided service, meaning I was receiving messages on the same service that was sending them.  When using the SQL Broker as middleware, in most cases you will be sending messages on one service, and receiving messages on another (two-sided).

In both cases, you absolutely must end the conversation.  Failing to do so, leaves the conversation out there, along with some space in the tempdb, and some portion of your CPU.  As more and more conversations are created, the worse and worse your problems get.

If you're experiencing this behavior, execute the following query to clear all conversations.

ALTER DATABASE myDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE myDB SET NEW_BROKER
ALTER DATABASE myDB SET MULTI_USER

After you get your SQL Server back, you'll want to update the code that receives the messages off the queue, to end the conversation after it has received the message.  From the sample in January, I'd change it from:

command.CommandText = "WAITFOR (RECEIVE CAST(message_body AS XML) FROM PersonChangeMessages);";

To:

command.CommandText = @"DECLARE @conversationHandle uniqueidentifier;
                        DECLARE @message AS XML;
                        WAITFOR (RECEIVE @conversationHandle = [conversation_handle],
                            @message = CAST(message_body AS XML) FROM PersonChangeMessages);
                            END CONVERSATION @conversationHandle;
                            SELECT @message;";

2.  Always Set the Lifetime on the Conversation

The next thing you'll always want to do, is provide a duration in which your conversation is valid for.  Conversations without a specified lifetime will last int.MaxValue in seconds.  So, quite some time.

Before we had the following:

BEGIN DIALOG @DialogHandle
FROM SERVICE [PersonChangeNotifications]
TO SERVICE 'PersonChangeNotifications'
ON CONTRACT [http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification]
WITH ENCRYPTION = OFF;
SEND ON CONVERSATION @DialogHandle
MESSAGE TYPE [http://schemas.microsoft.com/SQL/Notifications/QueryNotification] (@Message);

And we'll want to change that to:

BEGIN DIALOG @DialogHandle
FROM SERVICE [PersonChangeNotifications]
TO SERVICE 'PersonChangeNotifications'
ON CONTRACT [http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification]
WITH ENCRYPTION = OFF, LIFETIME = 30;
SEND ON CONVERSATION @DialogHandle
MESSAGE TYPE [http://schemas.microsoft.com/SQL/Notifications/QueryNotification] (@Message);

3.  Reuse Conversations and Conversation Groups

This last item is a bit more difficult to accomplish (thus I won't be providing a sample in this post), but it will insulate you from any conversations issues having a huge impact on your application's performance.  If there aren't that many conversations out there, you'll limit the overhead.  All conversations have a handle, or id, as we saw above.  If you persist this id, in the database, or in memory, you'll be able to reuse the conversation over and over, and reuse all the overhead created with the conversation.