Distributed Broker Queue Performance Issue

Hi,

We are doing a POC for transferring a huge number of messages(millions) from oner machine to another. The two approaches we are examining are MSMQ and SQL Broker. The MSMQ is set up as a remote queue on the target machine, and the source machine takes as little as 1 millisecond to send the message (using a .NET program). However, when testing on Service Broker, we find that the time taken to send message to the queue is significantly higher - like 70 millisecond. Could you please help us in understanding why this is happening?

The service broker distributed queues have been set up as per the directions in the posting at http://www.sqlservercentral.com/columnists/sindukuri/2797.asp

The source program (written in .NET) is calling a stored procedure in the source machine to write to the SSB queue. When we run SQL Trace, we find that the SP is responsible for 99% of the time taken. Here is our SP that send the message:

Declare @.ConversationHandle uniqueidentifier
Begin Dialog @.ConversationHandle
From Service SenderService
To Service 'ReceiverService'
On Contract SampleContract
WITH Encryption=off;
SEND
ON CONVERSATION @.ConversationHandle
Message Type SenderMessageType
(<<XML String>>)

Please let us know if there are any additional settings required in the Service Broker to improve its performance. Or , what are the other approaches for building a distributed SSB application?

How big are the messages?
Are you using transactional MSMQ?

70 ms per message means ~15 msgs per second, that is quite slow. The most basic setup w/o any optimization is usualy be around 200 msgs/sec (with 1k payload) and an optimal setup should drive ~4000 msgs/sec on comodity hardware (ie. .5 milliseconds per message). Of course, I'm talking fully transacted, remote delivery operations. Have you done the minimal optimizations needed for any database operations (eg. separate data and log files on separte spindles) ?

Sending one message per dialog will give you the worst performance in SSB, both on the sending side (you need to to add the cost of the END CONVERSATION messages) as well as from the point of view of processing the messages. Have a look at the slides at http://blogs.msdn.com/remusrusanu/archive/2007/04/03/orlando-slides-and-code.aspx for a comparison of one-message-per-conversation vs. resusing conversations, as well as to see the very basic optimizations you can apply to SSB operations.

Besides raw performance, the SSB semantics (dialogs/conversartions) are quite different from MSMQ. SSB provides full duplex exactly-once-in-order reliable delivery with well defined error semantics, while MSMQ provides just raw messaging (think TCP vs. UDP if you're familiar with IP network programming).|||

The messages are close to 1 K - these are simple XML strings. We are using transactional MSMQ, and transactional SSB. These transactions are created within the .NET program, and the SQL Stored Procedure does not contain any transactions. The logic is very simple - we have a loop in C#, that reads data from anothe table (and stores in a datareader), create XML representation of that data, and calls a Stored Procedure which contains the Send Statement. I examined the links that you provided - I understand that it is possible to delay the Commit by using loop counts. However, in our case, since there are separate transactions for each message, what we can do to improve throughput?

Regards

Prasanth

|||The problem is your performance of communicating from .Net application to SQL Server. You basically cannot feed more than 15 messages per second (15 T-SQL batches), so SSB has nothing else to transmit more than 15 messages per second. Anything you can do improve this will help the message rate: cumulate several calls into a single one to reduce the number of round trips, use parametrized queries, anything that applies generic .Net T-SQL programming applies to SSB as well.
If you cannot reduce the number of transaction commits, a faster spindle for the database LDF will help. Of course, separate MDF and LDF on separate spindles.