SEND

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 SEND command (and the BEGIN DIALOG command)

The SEND command was introduced to SQL Server when SQL Server 2005 was released.  It’s kind of like the INSERT command except that it is used only with the SQL Server Service Broker.  With the service broker you put data into a queue instead of a table.  Insert of inserting a command into the queue, you send a message to the queue, kind of like sending an email to someone else.  The service broker uses this same kind of idea (not really, but email is something that everyone gets, so it’s a half way decent anology).

 When you use the SEND commad you also have to use the BEGIN DIALOG command to get everything ready.  So before you can send the message you have to start a dialog, which then starts a conversation, with the service which will be doing the heavy lifting.  This is done with the BEGIN DIALOG command as shown in the code sample below.

 After you have the dialog handle in the @dialog_handle variable you use this handle to send the actual message to the conversation which was started when you begin the dialog.  In our example below we are just going to send the contents of the sys.tables DMV to the the queue.

DECLARE @xml AS XML
DECLARE @dialog_handle AS uniqueidentifierSET @xml = (SELECT * FROM sys.tables FOR XML AUTO)BEGIN DIALOG @dialog_handle
FROM SERVICE [tcp://codecamp/AW/sample_send_service]
TO SERVICE ‘tcp://codecamp/AW/sample_receive_service’
ON CONTRACT [tcp://codecamp/AW/sample_contract];SEND ON CONVERSATION @dialog_handle
 MESSAGE TYPE [tcp://codecamp/AW/sample_messagetype]
 (@xml);

Confused yet?  Shortly there will be a Back To Basics post about the RECEIVE command which is how you “read” the message.  At the end of that post I’ll include a link to some sample code which I’ve used a few times in my sessions which should help wrap this all up.

Denny