RECEIVE

Improving SQL Service Broker Performance

A while back I was talking to a Microsoft Support Engineer and he had mentioned that in a high load Service Broker environment such as ours there can be some impressive performance improvement can be achieved by reusing the service broker sessions.

The cost of creating and closing a new conversation for every message is about 4x, while the performance increase when receiving messages is about 10x.  Remus Rusanu talks more about the numbers and shows a solution for reusing conversations on his blog posting Reusing Conversations.

I liked Remus’s solution, but an issue that I had with it was that I didn’t want to have a different conversation for each spid.  If I used this method I would end up having hundreds of conversations open and I’d need a job to close them.  Within our application any number of events can trigger a service broker message to be sent off.  We usually have a few hundred threads logging in and out of the database at any one time.

This required that I take Remus’s solution and make it more flexible before moving it into our environment.  The solution that I’ve come up with supports a single conversation for each process within our application.  And at random intervals those conversations are closed, and new ones are started. Continued »

Back To Basics: The RECEIVE command (and END CONVERSATION)

The RECEIVE command is the last of the Service Broker items to cover in the Back To Basics series.  The RECEIVE command is what is used to actually get the message out of the queue so that you can process the data within the message_body column.  The RECEIVE command works just like the SELECT statement for the most part.  It has a FROM clause and a WHERE clause (which most people don’t use, but it’s there if you need it).  After you receive the message you will usually want to run the END CONVERSATION command.  This will tell the remote machine that you have receive the message and that no more messages should be sent on this conversation.  I usually only send a single message per conversation, so I don’t put in any logic to see if this is the last message in the conversation.

The basic syntax is very straight forward.

DECLARE @handle UNIQUEIDENTIFIER
DECLARE @message_body XML
RECEIVE TOP (1) @handle=conversation_handle, @message_body=cast(message_body as XML)
FROM [QueueName]END CONVERSATION @handleIt is required that you cast the message body to XML, as it's transmitted as binary data.  (If you are sending a single value or a binary blob then change the cast as needed.)  You don't have to write a second command to delete the message from the queue after you receive it.  The RECEIVE command handles the SELECT and DELETE in a single command.If you haven't been able to make to to one of my sessions, here is the sample source code which I use for the service broker session.  This ZIP file contains not only the sample code, but also the slide deck from the presentation which covers much of which I’ve covered in these blogs.  If you have any questions about the sample code please feel free to post those questions here so that everyone can see the answers.

I may take a little break from the Back To Basics posts for a little bit, but I haven’t really decided yet, so if you see more popping up on the site, I guess that means that I’ve decided to keep working the series for a bit.  If I do take a break, I will start back up shortly. 

I promise.

Denny