This update introduces an improvement to the Service Broker in SQL Server 2012 and SQL Server 2014. Before you apply this update, the maximum number of threads that the Service Broker uses to send messages to remote servers is double the number of CPUs that run the Service Broker. When there is heavy Service Broker task processing under the following conditions, this limit may not be adequate and SQL Server may become unresponsive to certain workloads:
- When many remote servers shut off, the threads that send messages to the remote servers wait for the Winsock time-out. Therefore, message delivery to remote servers that are online is delayed when the maximum number of the waiting threads is reached.
- When there is Service Broker activity in availability group databases, availability group synchronization workload may be degraded, if all service broker tasks are in use.
After you apply this update, you also need to contact Microsoft support to make some specific settings. Before this update, SQL Server only created two threads per physical CPU to handle Service Broker Tasks.
The following three trace flags were added to SQL Server 2012 Cumulative update 4 for SQL Server 2012 SP1.
- Trace Flag 8426 - The maximum number of the threads will be the number of physical CPU multiplied by 8.
- Trace Flag 8427 - The maximum number of the threads will be the number of physical CPU multiplied by 16.
- Trace Flag 8428 - The maximum number of the threads will be the number of physical CPU multiplied by 32.
Starting with SQL Server 2014, trace flags 8426 and 8427 are no longer available, only trace flag 8428 is available in SQL Server 2014 and later.
Note These trace flags need to be configured as startup parameters and require a restart of SQL Server to take effect.