Microsoft SQL Server 2012 Service Pack 1 (SP1) fixes are released as one downloadable file. Because the fixes are cumulative, each new release contains all the hotfixes and all the security fixes that were included with the previous SQL Server 2012 SP1 fix release.
Introduction
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.
Resolution
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.
Cumulative update information
The issue was first fixed in the following cumulative updates of SQL Server:
Each new cumulative update for SQL Server contains all the hotfixes and all the security fixes that were included with the previous cumulative update. We recommend that you download and install the latest cumulative updates for SQL Server:
Status
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.
References
For more information about the Incremental Servicing Model for SQL Server, click the following article number to go to the article in the Microsoft Knowledge Base:
935897 An Incremental Servicing Model is available from the SQL Server team to deliver hotfixes for reported problems For more information about the naming schema for SQL Server updates, click the following article number to go to the article in the Microsoft Knowledge Base:
822499 Naming schema for Microsoft SQL Server software update packages For more information about software update terminology, click the following article number to go to the article in the Microsoft Knowledge Base:
824684 Description of the standard terminology that is used to describe Microsoft software updates
