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

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.

Note for SQL Server 2016 You no longer have to enable trace flags in SQL Server 2016 because the auto-detection of the associated logic for trace flags is already incorporated into the product.

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 additional scalability fixes that were included with a cumulative update.
  • Table 3 contains recommendations and guidance for additional performance tuning.
Note For additional context, check following post from the SQL Server Support team:

Table 1. Important updates 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 Builds column.

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 considerTrace flag Required version/updateKnowledge 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
T8048SQL 2014 RTM, SQL 2012 RTMCSS escalation services blog post that covers this topic.
  • You are using features that rely on log pool cache (for example, Always On)
  • SQL Server is installed on systems with multiple sockets.
T9024SQL 2014 RTM, SQL 2012 SP1 CU32809338FIX: High "log write waits" counter value on an instance of SQL Server 2012

Note Trace flag 9024 isn’t required for SQL Server 2014 SP1 (and later service packs) or SQL Server 2012 SP3 (and later service packs)
  • Your instance of SQL Server is handling thousands of connection resets because of connection pooling.
T1236SQL 2014 RTM CU1, SQL 2012 SP1 CU92926217 FIX: Performance problems occur when database lock activity increases in SQL Server

Note Trace flag 1236 isn't required for SQL Server 2014 SP1 (and later service packs) or SQL Server 2012 SP3 (and later service packs)
  • 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.
T1118SQL 2014 RTM, SQL 2012 RTM328551 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.
T1117SQL 2014 RTM, SQL 2012 RTM2154845 Recommendations to reduce allocation contention in the SQL Server tempdb database
  • Performance issues because of SOS_PHYS_PAGE_CACHE and CMEMTHREAD waits during memory allocation on large-memory computers.
Not applicableSQL 2014 RTM CU1,

SQL 2012 SP1 CU9
2926223Performance problems occur in NUMA environments during foreign page processing in SQL Server 2012 or SQL Server 2014

Table 2. 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. Please be aware that some of these recommendations may require you to enable additional trace flags as startup parameters.

Note The CU name on the "Update method" 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.

#SymptomsRequired updateKnowledge Base article
1Eager writes during Select-into for temp tables causes performance issues.SQL 2012 SP2 CU1,

SQL 2012 SP1 CU10
2958012 Poor performance on I/O when you execute select into temporary table operation in SQL Server 2012
2You encounter PWAIT_MD_RELATION_CACHE or MD_LAZYCACHE_RWLOCK wait after an ALTER INDEX … ONLINE query operation is aborted.SQL 2014 RTM CU1,

SQL 2012 SP1 CU9
2926712 Performance decreases after an ALTER INDEX…ONLINE operation is aborted in SQL Server 2012 or SQL Server 2014
3Queries suddenly perform poorly on standard edition of the product. SQL 2014 RTM CU1

SQL 2012 SP1 CU7
2879373 Threads are not scheduled evenly in SQL Server 2012 or SQL Server 2014 Standard Edition
4Slow performance because of a sudden drop in Page life expectancy.SQL 2012 SP1 CU42845380You may experience performance issues in SQL Server 2012
5High CPU usage by resource monitor on systems with NUMA configuration, large memory, and "max server memory" set to a low value.SQL 2012 SP1 CU32813214CPU spike when there is no load on a server after you install SQL Server 2012 on the server
6Non-yielding scheduler while allocation memory for sort runs associated large memory grants on systems with large amount of memory installed.SQL 2012 SP1 CU22801379FIX: 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
7Non-yielding scheduler when the sort operator traverses many buckets in the buffer pool on systems with large memory.SQL 2012 SP1 CU12762557"Process <block list> appears to be non-yielding on Scheduler <ID>" error message when you run a query in SQL Server 2012
8High CPU usage when you run concurrent queries that take a long time to compile on systems with multiple NUMA nodes and many coresSQL 2012 SP2 CU1

SQL 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
9Memory allocations for sort operators take a long time to complete on NUMA systems with large memory because of remote node allocations.SQL 2012 SP1 CU32819662SQL Server performance issues in NUMA environments
10"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 2012 RTM CU12688697 Out-of-memory error when you run an instance of SQL Server 2012 on a computer that uses NUMA
11Spinlock contention on SOS_CACHESTORE and SOS_SELIST_SIZED_SLOCK when you build an index on spatial data type in a large table.SQL 2014 RTM CU1

SQL 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
12High CMEMTHREAD wait type when you build an index on a spatial data type in large tables.SQL 2014 RTM CU1

SQL 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
13Heavy SOS_CACHESTORE spinlock contention or your plans are being evicted frequently on ad hoc query workloads.Enable trace flag -T174

SQL 2014 RTM CU6, SQL 2012 SP1 CU 14
3026083 FIX: SOS_CACHESTORE spinlock contention on ad hoc SQL Server plan cache causes high CPU usage in SQL Server 2012 or 2014
14Executing concurrent data warehouse queries that take long compile time results in RESOURCE_SEMAPHORE_QUERY_COMPILE waits.Enable trace flag -T6498

SQL 2014 RTM CU 6
3024815 Large query compilation waits for RESOURCE_SEMAPHORE_QUERY_COMPILE in SQL Server 2014
15Existing statistics are not frequently updated because of the large number of rows in the table.Enable trace flag -T23712754171 Change auto update statistics threshold for tables with large number of rows
16CHECKDB command takes a long time for large databases.Enable trace flags -T2562 and -T25492634571Improvements for the DBCC CHECKDB command may result in faster performance when you use the PHYSICAL_ONLY option
17CHECKDB command takes a long time for large databases.SQL 2014 RTM CU63029825 FIX: DBCC CHECKDB take longer to run when SQL CLR UDTs are involved
18CHECKDB command takes a long time for large databases.Enable trace flag -T2566945770 FIX: The DBCC CHECKDB statement takes longer to run

Table 3.General considerations and best practices for improving performance of your instance of SQL Server

Review the content in the KB article/Books Online Resource column and consider implementing the guidance in the Recommended actions column.

#Knowledge Base article/Books Online resourceRecommended actions
1TF 8032

optimize for ad hoc workloads

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, consider the optimize for ad hoc workloads( server option and also the FORCED PARAMETERIZATION( database option.

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.
3955644 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 as 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
4918483 How to reduce paging of buffer pool memory in the 64-bit version of SQL ServerAssign the "Lock pages in memory" user right to the SQL service Startup account.

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.

Note You can use sp_configure to check the memory settings.
52207548 Slow Performance on Windows Server 2008 R2 when using the “Balanced” Power PlanReview the article, and work with your Windows administrator to implement one of the solutions that are noted in the "Resolution" section of the article.
6Instant File initializationWork 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.
7315512Considerations for the "autogrow" and "autoshrink" settings in SQL ServerCheck the current settings of your database and make sure that they are configured as per the recommendations in the Knowledge Base article.

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.
9Extended events

Consider using Extended events for performance monitoring and diagnostic data collection during troubleshooting. It takes advantage of the following improvements in recent SQL Server versions:
  • Lightweight infrastructure
  • Newer modes of data collection, data storage and processing (event file, ring buffer, event pairing, event counter, histogram)
  • Advanced capabilities during data collection (such as newer actions like call stack, larger set of events)
  • Dynamic filtering capabilities during data collection Scalability settings with advanced configuration (CPU/NUMA partitioning, buffer sizes, event loss)
  • Scalability settings with advanced configuration (CPU/NUMA partitioning, buffer sizes, event loss)

10The license core limitation for SQL Server 2012Consider upgrading your edition of SQL Server to a later SKU.
112506384 How to manually configure K-Group assignment on multiprocessor computersManually assign NUMA nodes to K-groups. For more information, check out the following:

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:


Third-party solution disclaimer

รหัสบทความ: 2964518 - การตรวจสอบครั้งสุดท้าย: 8 มี.ค. 2016 - ฉบับแก้ไข: 1