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.
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:
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:
And we'll want to change that to:
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.