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: