Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
Applies to: 
SQL Server
Azure SQL Managed Instance
The performance of a Service Broker application is generally determined by two factors:
- The number of messages arriving within a specified period of time
- The speed at which the application processes each message
Monitoring these two factors is the key to understanding the performance of the application.
Service Broker provides a set of performance counters that provide information on its activities. Service Broker also logs serious errors to the SQL Server error log and the Windows Application event log. For more information, see the following articles:
- Service Broker Related Dynamic Management Views (Transact-SQL)
- SQL Server, Broker Statistics object
- Broker Event Category
Tune a Service Broker stored procedure
Usually, tuning a stored procedure that uses Service Broker is no different from tuning any other stored procedure. However, there are a further considerations.
First, use the WAITFOR clause. Messages seldom arrive at predictable intervals. Even in a service where messages arrive at roughly the same rate that the stored procedure processes the messages, there might be times when no messages are available. Therefore, the procedure should use a WAITFOR clause with a RECEIVE statement or with a GET CONVERSATION GROUP statement. Without WAITFOR, these statements return immediately when there are no available messages on the queue. Depending on the implementation of the stored procedure, the procedure might then loop back through the statement, consuming resources needlessly, or the procedure might exit only to be reactivated shortly thereafter, consuming more resources than simply continuing to run.
You allow for the unpredictability in timing by using the WAITFOR clause with the RECEIVE or GET CONVERSATION GROUP statement. If your application runs continuously as a background service, you don't specify a timeout in the WAITFOR statement. If your application is activated by Service Broker or runs as a scheduled job, you specify a short timeout; for example, 500 milliseconds. An application that uses the WAITFOR statement gracefully handles unpredictable intervals between messages. Likewise, an activated application that exits after a short timeout doesn't consume resources when there are no messages to process.
Service Broker guarantees that only one instance of an application at a time can receive messages for conversations that share a conversation group identifier. Design your applications to take advantage of conversation group locking for synchronization. If your application maintains state, consider using the conversation group identifier to identify the state for the conversation. Process multiple messages for a conversation group in the same transaction. In general, however, only process messages for a single conversation group in a given transaction. This helps ensure that more than one instance of the application can process messages, even when the number of conversation groups is relatively small.
In addition, avoid using message retention. Maintaining a separate log table that saves the most important information from a message improves performance. Use message retention only when your application requires the exact messages sent and received.
Next, end conversations when the task completes. Service Broker maintains state for each active conversation. Although the amount of state for a particular conversation is small, an application that doesn't end conversations might suffer reduced performance over time.
Finally, keep transactions short. For example, if the conversation pattern for the service involves a large number of messages on the same conversation group, limiting the number of messages processed in each transaction might improve overall throughput.