Getting performance out of service broker procedures

Remus has written a great article on use of activation SPs and the way of achieving the best performance.

http://blogs.msdn.com/remusrusanu/archive/2006/10/14/writing-service-broker-procedures.aspx

I am looking into this as a means of asynchronous logging and so have taken Remus's script and expanded on it.

Remus's script always has 100 conversations with 100 messages per conversation. You need to be aware that the RECIEVE statement will only RECIEVE messages from ONE conversation group (by default all conversations are in their own conversation group)

What does that mean? Well the solutions that Remus's puts forward are about doing batch processing on messages. The gains are huge by batching up results, however a batch is a conversation group. That means if your conversation group only has 1 message in your batch will be 1 row. Not much of a batch, similarly if your conversation group has 10000 messages in it your batch can have 10000 rows in it.

If you re-run Remus's scripts with different conversation sizes the results show that as the size tends to 1 then the performance of all the options become similar (the batch comit providing the only gain). Also as the batch size increases the gain obtained by using the batch solutions increases to 1700%.

So what does this mean?

You need to put as many messages/conversations in a conversation group as possible. You obviously need to test your situation to find the optmium size.

You can achieve this by reusing an existing conversation group is one already exists. Have a look in sys.conversation_endpoints.



-
Published Tuesday, October 17, 2006 12:03 AM by simonsabin

Comments

Tuesday, October 17, 2006 12:38 AM by SimonS SQL Server Stuff

# SB - Its expensive to start a conversation

I've just mentioned Remus's post on service broker procedures. The other thing that came up when...
Tuesday, October 17, 2006 12:38 AM by SimonS' SQL Server Stuff

# SB - Its expensive to start a conversation

I've just mentioned Remus's post on service broker procedures. The other thing that came up when...