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.
- 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 together with a cumulative update.
- Table 3 contains recommendations and guidance for additional performance tuning.
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 version/update 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 consider||Trace flag||Required version/update||Knowledge Base article/Blog link that provides more details|
|1|| ||T8048||SQL 2014 RTM, SQL 2012 RTM||CSS escalation services blog post that covers this topic.|
|2|| ||T9024||SQL 2014 RTM, SQL 2012 SP1 CU3||2809338FIX: 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)
|3|| ||T1236||SQL 2014 RTM CU1, SQL 2012 SP1 CU9||2926217 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)
|4|| ||T1118||SQL 2014 RTM, SQL 2012 RTM||328551 Concurrency enhancements for the tempdb database |
Note Enable the trace flag and add multiple data files for the tempdb database.
|5|| ||T1117||SQL 2014 RTM, SQL 2012 RTM||2154845 Recommendations to reduce allocation contention in the SQL Server tempdb database|
|6|| ||Not applicable||SQL 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 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|
|1||Eager 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|
|2||You 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|
|3||Queries 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|
|4||Slow performance because of a sudden drop in Page life expectancy.||SQL 2012 SP1 CU4||2845380You may experience performance issues in SQL Server 2012|
|5||High CPU usage by resource monitor on systems with NUMA configuration, large memory, and "max server memory" set to a low value.||SQL 2012 SP1 CU3||2813214CPU spike when there is no load on a server after you install SQL Server 2012 on the server|
|6||Non-yielding scheduler while allocation memory for sort runs associated large memory grants on systems with large amount of memory installed.||SQL 2012 SP1 CU2||2801379FIX: 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|
|7||Non-yielding scheduler when the sort operator traverses many buckets in the buffer pool on systems with large memory.||SQL 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|
|8||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 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|
|9||Memory allocations for sort operators take a long time to complete on NUMA systems with large memory because of remote node allocations.||SQL 2012 SP1 CU3||2819662SQL 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 CU1||2688697 Out-of-memory error when you run an instance of SQL Server 2012 on a computer that uses NUMA|
|11||Spinlock 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|
|12||High 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|
|13||Heavy 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|
|14||Executing 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|
|15||Existing statistics are not frequently updated because of the large number of rows in the table.||Enable trace flag -T2371||2754171 Change auto update statistics threshold for tables with large number of rows|
|16||CHECKDB command takes a long time for large databases.||Enable trace flags -T2562 and -T2549||2634571Improvements for the DBCC CHECKDB command may result in faster performance when you use the PHYSICAL_ONLY option|
|17||CHECKDB command takes a long time for large databases.||SQL 2014 RTM CU6||3029825 FIX: DBCC CHECKDB take longer to run when SQL CLR UDTs are involved|
|18||CHECKDB command takes a long time for large databases.||Enable trace flag -T2566||945770 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 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|
|1||TF 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(http://msdn.microsoft.com/en-us/library/cc645587.aspx) server option and also the FORCED PARAMETERIZATION(http://technet.microsoft.com/en-us/library/ms175037.aspx) 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.|
|3||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
Starting in Microsoft SQL Server 2016, this recommendation no longer applies.
|4||918483 How to reduce paging of buffer pool memory in the 64-bit version of SQL Server||Assign 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.
|5||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.|
|6||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.|
|7||315512Considerations 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.|
|8||ALTER WORKLOAD GROUP ||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.|
|9||Extended 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: |
|10||The license core limitation for SQL Server 2012||Consider upgrading your edition of SQL Server to a later SKU.|
|11||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:|
- If all the conditions in the Table 1 apply to you:
- 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:
- DBCC (Transact-SQL)
- Trace Flags (Transact-SQL)
- How to obtain the latest service pack for SQL Server 2012
- Where to find information about the latest SQL Server builds
- SQL Server community resources on important updates for SQL Server
Article ID: 2964518 - Last Review: Aug 15, 2017 - Revision: 4