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

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
  • You encounter high CMEMTHREAD waits.
  • SQL Server is installed on systems with 8 or more cores per socket.
T8048
  • SQL Server 2012 RTM to current Service Pack (SP)/CU
  • SQL Server 2014 RTM to SP1
  • SQL Server 2014 SP2 to current SP/CU
  • SQL Server 2016 RTM to current SP/CU
  • SQL Server 2017 RTM to current SP/CU
  • You encounter high CMEMTHREAD waits.
  • SQL Server is installed on systems with 8 or more cores per socket.
T8079 SQL Server 2014 SP2 to current SP/CU
  • SQL Server 2016 RTM to current SP/CU
  • SQL Server 2017 RTM to current SP/CU
  • You are using features that rely on log pool cache. (for example, Always On)
  • SQL Server is installed on systems with multiple sockets.
T9024 Cumulative update package 3 for SQL Server 2012 Service Pack 1 to SP2 SQL Server 2014 RTM
  • SQL Server 2012 SP3 to current SP/CUSQL
  • Server 2014 SP1 to current SP/CU
  • SQL Server 2016 RTM to current SP/CU
  • SQL Server 2017 RTM to current SP/CU
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
  • SQL Server 2012 SP3 to current SP/CUSQL
  • Server 2014 SP1 to current SP/CUSQL
  • Server 2016 RTM to current SP/CU
  • SQL Server 2017 RTM to current SP/CU
  • Your application workload involves frequent tempdb usage (creation and drop of temp tables or table variables).
  • You notice user requests waiting for tempdb page resources because of allocation contention.
T1118
  • SQL Server 2012 RTM to current SP/CU
  • SQL Server 2014 RTM to current SP/CU
  • SQL Server 2016 RTM to current SP/CU
  • SQL Server 2017 RTM to current SP/CU
Concurrency enhancements for the tempdb database

NOTE Enable the trace flag and add multiple data files for the tempdb database.
  • You have multiple tempdb data files.
  • The data files at first are set to the same size.
  • Because of heavy activity, tempdb files encounter growth and not all files grow at the same time and cause allocation contention.
T1117
  • SQL Server 2012 RTM to current SP/CU
  • SQL Server 2014 RTM to current SP/CU
  • SQL Server 2016 RTM to current SP/CU
  • SQL Server 2017 RTM to current SP/CU
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
  • Entries in the plan cache are evicted because of growth in other caches or memory clerks
  • High CPU consumption due to frequent recompiles of queries
T8032
  • SQL Server 2012 RTM to current SP/CU
  • SQL Server 2014 RTM to current SP/CU
None
Existing statistics are not frequently updated because of the large number of rows in the table. T2371
  • SQL Server 2012 RTM to current SP/CU
  • SQL Server 2014 RTM to current SP/CU
None
  • Statistics jobs take a long time to complete.
  • Cannot execute multiple statistics update jobs in parallel.
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.
  • T2562
  • T2549
    • SQL Server 2012 RTM to current SP/CU
    • SQL Server 2014 RTM to current SP/CU
    None
    CHECKDB command takes a long time for large databases. T2566
    • SQL Server 2012 RTM to current SP/CU
    • SQL Server 2014 RTM to current SP/CU
    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
    • SQL Server 2014 SP2 to current SP/CUSQL
    • Server 2016 RTM to current SP/CU
    • SQL Server 2017 RTM to current SP/CU
    You are troubleshooting specific query performance issues Optimizer fixes are disabled by default. T4199
    • SQL Server 2012 RTM to SP4
    • SQL Server 2014 RTM to latest
    None
    You experience slow performance using query operations with spatial data types.
    • T6532
    • T6533
    • T6534
    • SQL Server 2012 SP3 to current SP/CU
    • SQL Server 2014 SP2 to current SP/CU
      • SQL Server 2016 RTM to current SP/CU
      • SQL Server 2017 RTM to current SP/CU
        • Queries encounter SOS_MEMORY_TOPLEVELBLOCKALLOCATOR and CMEMTHREAD waits.
        • There is low available virtual address space for the SQL Server process.
        T8075
        • SQL Server 2012 SP2 CU8 to current SP/CU
        • SQL Server 2014 RTM CU10 to current SP/CU
        • SQL Server 2016 RTM to current SP/CU
        • SQL Server 2017 RTM to current SP/CU
        FIX: Out of memory error when the virtual address space of the SQL Server process is low in SQL Server
        • SQL Server is installed on a machine with large amounts of memory.
        • Creating new databases takes a long time.
        T3449
        • SQL Server 2012 SP3 CU3 to current SP/CU
        • SQL Server 2014 RTM CU14 to current RTM CU
        • SQL Server 2014 SP1 CU7 to current SP/CU
        • SQL Server 2016 RTM to current SP/CU
        • SQL Server 2017 RTM to current SP/CU
        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.

        Symptoms Required update Knowledge Base article
        Eager writes during Select-into for temp tables causes performance issues. SQL Server 2012 SP2 CU1
        SQL Server 2012 SP1 CU10
        FIX: Poor performance on I/O when you execute select into temporary table operation in SQL Server 2012
        You encounter PWAIT_MD_RELATION_CACHE or MD_LAZYCACHE_RWLOCK wait after an ALTER INDEX ... ONLINE query operation is aborted. SQL Server 2014 RTM CU1
        SQL Server 2012 SP1 CU9
        FIX: Performance decreases after an ALTER INDEX…ONLINE operation is aborted in SQL Server 2012 or SQL Server 2014
        Queries suddenly perform poorly on standard edition of the product. SQL Server 2014 RTM CU1
        SQL Server 2012 SP1 CU7
        FIX: Threads are not scheduled evenly in SQL Server 2012 or SQL Server 2014 Standard Edition
        Slow performance because of a sudden drop in Page life expectancy. SQL Server 2012 SP1 CU4 FIX: You may experience performance issues in SQL Server 2012
        High CPU usage by resource monitor on systems with NUMA configuration, large memory, and "max server memory" set to a low value. SQL Server 2012 SP1 CU3 FIX: CPU spike when there is no load on a server after you install SQL Server 2012 on the server
        Non-yielding scheduler while allocation memory for sort runs associated large memory grants on systems with large amount of memory installed. SQL Server 2012 SP1 CU2 FIX: Error 17883 when you run a query on a server that has many CPUs and a large amount of memory in SQL Server 2012 or in SQL Server 2008 R2
        Non-yielding scheduler when the sort operator traverses many buckets in the buffer pool on systems with large memory. SQL Server 2012 SP1 CU1 FIX: "Process appears to be non-yielding on Scheduler " error message when you run a query in SQL Server 2012
        High CPU usage when you run concurrent queries that take a long time to compile on systems with multiple NUMA nodes and many cores. SQL Server 2012 SP2 CU1
        SQL Server 2014 RTM CU2
        FIX: Intense query compilation workload does not scale with growing number of cores on NUMA hardware and results in CPU saturation in SQL Server
        Memory allocations for sort operators take a long time to complete on NUMA systems with large memory because of remote node allocations. SQL Server 2012 SP1 CU3 FIX: SQL Server performance issues in NUMA environments
        Out of memory errors when SQL Server is installed on a NUMA machine with large amount of RAM and SQL Server has lots of foreign pages. SQL Server 2012 RTM CU1 FIX: Out-of-memory error when you run an instance of SQL Server 2012 on a computer that uses NUMA
        Spinlock contention on SOS_CACHESTORE and SOS_SELIST_SIZED_SLOCK when you build an index on spatial data type in a large table. SQL Server 2014 RTM CU1
        SQL Server 2012 SP1 CU7
        FIX: Slow performance in SQL Server 2012 or SQL Server 2014 when you build an index on a spatial data type of a large table
        High CMEMTHREAD wait type when you build an index on a spatial data type in large tables. SQL Server 2014 RTM CU1
        SQL Server 2012 SP1 CU7
        FIX: Slow performance in SQL Server when you build an index on a spatial data type of a large table in a SQL Server 2012 or SQL Server 2014 instance
        Performance issues because of SOS_PHYS_PAGE_CACHE and CMEMTHREAD waits during memory allocation on large-memory computers. SQL Server 2014 RTM CU1
        SQL Server 2012 SP1 CU9
        FIX: Performance problems occur in NUMA environments during foreign page processing in SQL Server 2012 or SQL Server 2014
        CHECKDB command takes a long time for large databases. Cumulative update package 6 for SQL Server 2014 FIX: DBCC CHECKDB/CHECKTABLE command may take longer in SQL Server 2012 or SQL Server 2014

        Important notes

        References

        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