A common use of service broker is the "data push" scenario in which messages are asynchronously sent to a destination such as a data warehouse for storage and processing with minimal processing delay to the source application. Two frequent concerns are whether service broker can handle a proposed work load, and how to "tune" a broker application so that it can achieve the required performance. Since various applications impose different constraints and are hosted within differing computing and networking configurations, there is no "one size fits all" answer.
This sample offers a means of estimating the performance of a broker application and tuning it to suit a given load and configuration. The user does this by setting several application parameters, such as message volume, message size, and processing time, as well as several internal parameters, such as number of initiator transactions, number of dialogs, etc. On the initiator, the output is the time to send the specified number of messages and the time for the messages to be transmitted to the target. On the target, the output is the time to receive and process the messages, which allows the user to obtain an estimate of whether the initiator is overrunning the target, something to avoid for a sustained high volume message load.
The sample also illustrates a number of recommended practices for using service broker, and can serve as an example of how to build the service broker part of a data push application. Of particular significance, it is recommended that batch messaging be done where possible. On the initiator side, this refers to sending messages on a set of "reusable" dialogs to avoid the overhead of creating a dialog per message. The dialog pool sample shows how to do this. On the target, batching refers to receiving a set of messages at a time, which can significantly improve performance.
Running the Sample
This sample is normally run between two server instances on different machines using Windows transport security. However, it can easily be configured to perform a "loop around" data push in the same database by skipping the indicated sections of the initiator and target setup scripts. For the two server case, it is essential that the servers are configured to enable communication protocols. In this example, we will be using TCP, so use the SQL Server Configuration Manager to make sure TCP is enabled on both servers.
On each server, open the project file in SQL Server Management Studio.
Edit the Common setup.sql script and set the desired parameters. Make sure the edits are performed identically on both servers.
Run the scripts, in order:
1 - Common setup.sql (both servers).
2 - Initiator setup.sql.
3 - Target setup.sql.
4 - Initiator send.sql. The message sending start and end times are printed.
5 - Target monitor.sql. The message processing time is printed.
6 - Cleanup.sql (both servers).
© 2008 Microsoft Corporation. All rights reserved.