SQLTop*, lightweight, simple and easy to use.

When a performance issue occurs, SQLTop* will show quick and easy entire SQL Server Performance on real-time, including waits, performance counters and sessions.

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:

Frequently used knobs to tune a busy SQL Server

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 consider	Trace flag	Required version/update	Knowledge Base article/Blog link that provides more details
1	You encounter high CMEMTHREAD waits. SQL Server is installed on systems with 8 or more cores per socket	T8048	SQL 2014 RTM, SQL 2012 RTM	CSS escalation services blog post that covers this topic.
2	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 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	Your instance of SQL Server is handling thousands of connection resets because of connection pooling.	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	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 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	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 2014 RTM, SQL 2012 RTM	2154845Recommendations to reduce allocation contention in the SQL Server tempdb database
6	Performance issues because of SOS_PHYS_PAGE_CACHE and CMEMTHREAD waits during memory allocation on large-memory computers.	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 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. 

#	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 anALTER 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_CACHESTOREand 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 inRESOURCE_SEMAPHORE_QUERY_COMPILEwaits.	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 KB article/Books Online Resource column and consider implementing the guidance in theRecommended actions column. 

#	Knowledge Base article/Books Online resource	Recommended actions
1	TF 8032 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, 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.
2	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.
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 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
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	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.
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: 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)
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: NUMA node proximity

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:
  How to determine the version and edition of SQL Server and its components