Recommended updates and configuration options for SQL Server with high-performance workloads
This article includes a list of performance improvements and configuration options that are available for SQL Server 2012 and later versions.
Original product version: SQL Server 2014, SQL Server 2012
Original KB number: 2964518
Apply the recommended updates and improve the performance of SQL Server 2014 and SQL Server 2012
This article describes the performance improvements and changes that are available for SQL Server 2014 and SQL Server 2012 versions through various product updates and configuration options. You can consider applying these updates in order to improve the performance of the instance of SQL Server. The degree of improvement that you see will depend on various factors that include workload pattern, contention points, processor layout (number of processor groups, sockets, NUMA nodes, cores in a NUMA node) and amount of memory present in the system. SQL Server support team has used these updates and configuration changes to achieve reasonable performance gains for customer workloads that used hardware systems that had several NUMA nodes and lots of processors. The support team will continue to update this article with other updates in the future.
High-end systems A high-end system typically has multiple sockets, eight cores or more per socket, and a half terabyte or more of memory.
Note
In SQL Server 2016 and later versions, many of the trace flags mentioned in this article is the default behavior and you don't have to enable them in those versions.
The recommendations are grouped into three tables as follows:
- Table 1 contains the most frequently recommended updates and trace flags for scalability on high-end systems.
- Table 2 contains recommendations and guidance for additional performance tuning.
- Table 3 contains additional scalability fixes that were included together with a cumulative update.
Table 1. Important updates and trace flags for high- end systems
Review the following table and enable the trace flags in the Trace flag column after you make sure that your instance of SQL Server meets the requirements in the Applicable Version and build ranges column.
Note
Applicable Version and build indicates the specific update in which the change or trace flag was introduced. If no CU is specified, then all CU's in the SP are included.
Not Applicable Version and build indicates the specific update in which the change or trace flag became the default behavior. Therefore, just applying that update will be enough to get the benefits.
Important
When you enable fixes with trace flags in Always On environments, please be aware that you have to enable the fix and trace flags on all the replicas that are part of the Availability Group.
| Scenario and symptom to consider | Trace flag | Applicable Version and build ranges | Not Applicable Version and build ranges | Knowledge Base article/Blog link that provides more details |
|---|---|---|---|---|
|
T8048 |
|
|
|
|
T8079 | SQL Server 2014 SP2 to current SP/CU |
|
|
|
T9024 | Cumulative update package 3 for SQL Server 2012 Service Pack 1 to SP2 SQL Server 2014 RTM |
|
FIX: High "log write waits" counter value on a SQL Server 2012 or SQL Server 2014 instance |
| Your instance of SQL Server is handling thousands of connection resets because of connection pooling. | T1236 | Cumulative update package 9 for SQL Server 2012 Service Pack 1 to SP2 Cumulative Update 1 for SQL Server 2014 |
|
|
|
T1118 |
|
|
Concurrency enhancements for the tempdb database NOTE Enable the trace flag and add multiple data files for the tempdb database. |
|
T1117 |
|
|
Recommendations to reduce allocation contention in SQL Server tempdb database |
Heavy SOS_CACHESTORE spinlock contention or your plans are being evicted frequently on ad hoc query workloads. |
T174 |
|
None |
|
|
T8032 |
|
None |
|
| Existing statistics are not frequently updated because of the large number of rows in the table. | T2371 |
|
None | |
|
T7471 | SQL Server 2014 SP1 CU6 to current SP/CU | None | Boosting Update Statistics performance with SQL 2014 & SQL 2016 |
| CHECKDB command takes a long time for large databases. |
|
|
None | |
| CHECKDB command takes a long time for large databases. | T2566 |
|
None |
|
Executing concurrent data warehouse queries that take long compile-time results in RESOURCE_SEMAPHORE_QUERY_COMPILE waits. |
T6498 | Cumulative update package 6 for SQL Server 2014 to SP1 |
|
|
| You are troubleshooting specific query performance issues Optimizer fixes are disabled by default. | T4199 |
|
None | |
| You experience slow performance using query operations with spatial data types. |
|
|
|
|
|
T8075 |
|
|
FIX: Out of memory error when the virtual address space of the SQL Server process is low in SQL Server |
|
T3449 |
|
|
FIX: SQL Server database creation on a system with a large volume of memory takes longer than expected |
Table 2. General considerations and best practices for improving performance of your instance of SQL Server
Review the content in the Knowledge Base article/Books Online Resource column and consider implementing the guidance in the Recommended actions column.
| Knowledge Base article/Books Online resource | Recommended actions |
|---|---|
| Configure the max degree of parallelism Server Configuration Option | Use the sp_configure stored procedure to make configuration changes to Configure the max degree of parallelism Server Configuration Option for your instance of SQL Server as per the Knowledge Base article. |
| Compute capacity limits by edition of SQL Server | Enterprise Edition with Server + Client Access License (CAL) licensing is limited to 20 cores per SQL Server instance. There are no limits under the Core-based Server Licensing model. Consider upgrading your edition of SQL Server to the appropriate SKU to leverage all hardware resources. |
| Slow Performance on Windows Server when using the "Balanced" Power Plan | Review the article, and work with your Windows administrator to implement one of the solutions that are noted in the "Resolution" section of the article. |
| Manually assign NUMA nodes to K-groups. | |
| Optimize for ad hoc workloads FORCED PARAMETERIZATION | Entries in the plan cache are evicted because of growth in other caches or memory clerks. You might also encounter plan cache eviction when the cache reaches its maximum number of entries. In addition to trace flag 8032 discussed above, consider the optimize for ad hoc workloads server option and also the FORCED PARAMETERIZATION database option. |
| How to reduce paging of buffer pool memory in SQL Server Memory configuration and sizing considerations in SQL Server 2012 and later versions | Assign the Enable the Lock Pages in Memory Option (Windows) user right to the SQL service Startup account. See How to enable the "locked pages" feature in SQL Server 2012. Set maximum server memory to approximately 90 percent of total physical memory. Make sure that the Server memory configuration options setting accounts for memory from only the nodes that are configured to use affinity mask settings. |
| SQL Server and Large Pages Explained... Tuning options for SQL Server when running in high performance workloads | Consider enabling TF 834 if you have a server with a large amount of memory, particularly with an analytical or data warehousing workload. Keep in mind that TF 834 is not recommended if you are using columnstore indexes. |
| Description of the "access check cache bucket count" and "access check cache quota" options that are available in the sp_configure stored procedure | Use access check cache Server Configuration Options to configure these values per the recommendations in the Knowledge Base article. Recommended values for high-end systems are as follows: "access check cache bucket count": 256 "access check cache quota": 1024 |
| ALTER WORKLOAD GROUP Memory grant query hints | If you have many queries that are exhausting large memory grants, reduce request_max_memory_grant_percent for the default workload group in the resource governor configuration from the default 25 percent to a lower value. New query memory grant options are available (min_grant_percent and max_grant_percent) in SQL Server |
| Instant File initialization | Work with your Windows administrator to grant the SQL Server service account the "Perform Volume Maintenance Tasks" user right as per the information in the Books Online topic. |
| Considerations for the "autogrow" and "autoshrink" settings in SQL Server | Check the current settings of your database and make sure that they are configured as per the recommendations in the Knowledge Base article. |
| Database Checkpoints (SQL Server) | Consider enabling indirect checkpoints on user databases to optimize I/O behavior in SQL Server 2012 and 2014. |
| FIX: Slow synchronization when disks have different sector sizes for primary and secondary replica log files in SQL Server AG and Logshipping environments | If you have an Availability Group where the transaction log on the primary replica is on a disk with 512-byte sector size and the secondary replica's transaction log is on a drive with 4K sector size, you may have an issue where synchronization is slow. In these cases, enabling TF 1800 should correct the issue. For more information, see Trace Flag 1800. |
| If your SQL Server is not already CPU bound and a 1.5% to 2% overhead is negligible for your workloads, we recommend you enable TF 7412 as a startup trace flag. This flag enables lightweight profiling in SQL Server 2014 SP2 or later, which will give you the ability to do live query troubleshooting in production environments. |
Table 3. Performance fixes that are included in a cumulative update
Review the description in the Symptoms column and apply the required updates in the Required update column in applicable environments. You can review the Knowledge Base article for more information about the respective issues. These recommendations do not require you to enable additional trace flags as startup parameters. Just applying the latest Cumulative Update or Service Pack that includes these fixes is enough to get the benefit.
Note
The CU name in the Required update column provides the first cumulative update of SQL Server that resolves this issue. A cumulative update contains all the hotfixes and all the updates that were included with the previous SQL Server update release. Therefore, we recommend that you install the latest cumulative update in order to resolve the issues.
Important notes
If all the conditions in the Table 1 apply to you:
- Guidance for SQL Server 2014: Apply at least Cumulative Update 1 for SQL Server 2014 for RTM and add "-T8048 -T9024 -T1236 -T1117 -T1118" to SQL Server start up parameter list.
- Guidance for SQL Server 2012: Apply SP2 and add "-T8048 -T9024 -T1236 -T1117 -T1118" to SQL Server start up parameter list.
For general information about how to use trace flags, check the DBCC TRACEON - Trace Flags (Transact-SQL) topic in SQL Server Books Online.
You can find more information about number of processors, NUMA configuration, and so on, in your View the SQL Server error log in SQL Server Management Studio (SSMS).
To find the version of SQL Server, check the following:
How to determine the version and edition of SQL Server and its components
References
Where to find information about the latest SQL Server builds
SQL Server community resources on important updates for SQL Server
Applies to
- SQL Server 2014 Enterprise
- SQL Server 2014 Enterprise Core
- SQL Server 2014 Business Intelligence
- SQL Server 2014 Developer
- SQL Server 2014 Standard
- SQL Server 2014 Web
- SQL Server 2014 Express
- SQL Server 2012 Business Intelligence
- SQL Server 2012 Developer
- SQL Server 2012 Enterprise
- SQL Server 2012 Standard
- SQL Server 2012 Web
- SQL Server 2012 Enterprise Core