Recommended updates and configuration options for SQL Server 2012 and SQL Server 2014 with high-performance workloads

Applies to: SQL Server 2014 EnterpriseSQL Server 2014 Enterprise CoreSQL Server 2014 Business Intelligence More

This article describes the performance improvements and changes that are available for Microsoft SQL Server 2012 and SQL Server 2014 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 which 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. Microsoft 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.
 

How to apply the recommended updates and improve the performance of SQL Server 2012 and SQL Server 2014


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.
Note For additional context, check following post from the SQL Server Support team:


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 Required version/update column.

Notes:

  • 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
CSS escalation services blog post that covers this topic.
  • 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

Configure SQL Server to Use Soft-NUMA (SQL Server)

SQL Server 2014 Service Pack 2 is now Available
  • You are using features that rely on log pool cache (for example, Always On)
  • SQL Server is installed on systems with multiple sockets.
T9024
  • SQL Server 2012 SP3 to current SP/CU
  • SQL Server 2014 SP1 to current SP/CU
  • SQL Server 2016 RTM to current SP/CU
  • SQL Server 2017 RTM to current SP/CU
2809338 FIX: High "log write waits" counter value on an instance of SQL Server 2012
  • Your instance of SQL Server is handling thousands of connection resets because of connection pooling.
T1236
  • SQL Server 2012 SP3 to current SP/CU

  • SQL Server 2014 SP1 to current SP/CU

  • SQL Server 2016 RTM to current SP/CU

  • SQL Server 2017 RTM to current SP/CU

2926217 FIX: Performance problems occur when database lock activity increases in SQL Server
  • 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 [See Note]
  • 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

328551 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

2154845 Recommendations to reduce allocation contention in the SQL Server tempdb database
  • Heavy SOS_CACHESTORE spinlock contention or your plans are being evicted frequently on ad hoc query workloads.
T174
  • None

Documentation of TF 8032

See Cache Size Management section of Plan Cache Internals
  • 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

Documentation of TF 8032

See Cache Size Management section of Plan Cache Internals
  • 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
2754171 Change auto update statistics threshold for tables with large number of rows
  • 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

3156157 Running multiple UPDATE STATISTICS for different statistics on a single table concurrently is available

Boosting Update Statistics performance with SQL Server 2014 & SQL Server 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
2634571 Improvements for the DBCC CHECKDB command may result in faster performance when you use the PHYSICAL_ONLY option
  • 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

See T2566 details in DBCC TRACEON - Trace Flags.

A faster CHECKDB in SQL Server (Part IV)

  • Executing concurrent data warehouse queries that take long compile time results in RESOURCE_SEMAPHORE_QUERY_COMPILE waits.
T6498
  • SQL Server 2014 SP2 to current SP/CU
  • SQL Server 2016 RTM to current SP/CU
  • SQL Server 2017 RTM to current SP/CU
3024815 Large query compilation waits for RESOURCE_SEMAPHORE_QUERY_COMPILE in SQL Server 2014
  • 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
974006 SQL Server query optimizer hotfix trace flag 4199 servicing model
  • 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
3107399 Spatial performance improvements in SQL Server 2012 and 2014
  • Queries encounter SOS_MEMORY_TOPLEVELBLOCKALLOCATOR and CMEMTHREAD waits
  • There is very 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

3074434 Out of memory error when the virtual address space of the SQL Server process is very low on available memory

4077105 Out of memory error when the virtual address space of the SQL Server process is very low

  • 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
3158396 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
  • 2806535 Recommendations and guidelines for the "max degree of parallelism" configuration option in SQL Server
Use the sp_configure stored procedure to make configuration changes to "max degree of parallelism" for your instance of SQL Server as per the Knowledge Base article.
  • 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.
  • 2207548 Slow Performance on Windows Server 2008 R2 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.
  • 2506384 How to manually configure K-Group assignment on multiprocessor computers
Manually assign NUMA nodes to K-groups. For more information, check out the following article:
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.
  • 918483 How to reduce paging of buffer pool memory in the 64-bit version of SQL Server
  • 2663912 Memory configuration and sizing considerations in SQL Server 2012 and 2014
  1. Assign the "Lock pages in memory" user right to the SQL service Startup account. See How to enable the "locked pages" feature in SQL Server 2012.
  2. Set maximum server memory to approximately 90 percent of total physical memory. Make sure that the "max server memory" setting accounts for memory from only the nodes that are configured to use affinity mask settings.
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.
  • 955644 Description of the "access check cache bucket count" and "access check cache quota" options that are available in the sp_configure stored procedure
Use sp_configure 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
 
  • 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
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.
  • 315512 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.
Consider enabling indirect checkpoints on user databases to optimize I/O behavior in SQL Server 2012 and 2014. 
  • 3009974 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 very slow.  In these cases, enabling TF 1800 should correct the issue.
  • Lightweight Profiling
  • 3170113 Update to expose per-operator query execution statistics in showplan XML and Extended Event in SQL Server 2014 SP2
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
2958012 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
2926712 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
2879373 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 2845380 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 2813214 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 2801379 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 2762557 "Process <block list> appears to be non-yielding on Scheduler <ID>" 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
2928300 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 2819662 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 2688697 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
2887899 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
2887888 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 a 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

2926223 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. SQL Server 2014 RTM CU6 3029825 FIX: DBCC CHECKDB take longer to run when SQL CLR UDTs are involved

Important notes

  • If all the conditions in the Table 1 apply to you:
     
    • Guidance for SQL Server 2014: Apply at least CU1 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 Trace Flags topic in SQL Server Books Online.
  • You can find more information about number of processors, NUMA configuration, and so on in your SQL Server Errorlog.
  • To find the version of SQL Server, check the following: