Recommended updates and configuration options for SQL Server 2017 and 2016 with high-performance workloads

Applies to: SQL Server 2017Microsoft SQL Server 2016

This article describes the performance improvements and changes that are available for Microsoft SQL Server 2017 and SQL Server 2016 through various product updates and configuration options.
We recommend that you consider applying these updates to improve the performance of SQL Server instances. The degree of improvement will depend on various factors, including workload pattern, contention points, processor layout (number of processor groups, sockets, NUMA nodes, and cores in a NUMA node), and the amount of available memory in the system.
The Microsoft SQL Server support team has used these updates and configuration changes to achieve reasonable performance gains for customer workloads that use hardware systems that included several NUMA nodes and lots of processors. The support team will continue to update this article with other updates in the future.

How to apply recommended updates and improve SQL Server performance


These recommendations for improving the performance of SQL Server 2017 and SQL Server 2016 are grouped into five 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 information about changes in behavior and default settings in SQL 2017 and 2016.
  • Table 4 contains additional scalability fixes that were included together with a cumulative update (CU).
  • Table 5 contains recommended fixes and configuration guidelines for SQL Server instances deployed in a Linux environment.
Note For additional context, see following CSS SQL Server Engineers Blog article: