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

This article describes a list of performance improvements and configuration options that are available for SQL Server 2016 and later versions.

Original product version:   SQL Server 2017 on Windows, SQL Server 2016
Original KB number:   4465518

Introduction

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 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.

Definition: 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.

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 Frequently used knobs to tune a busy SQL Server.

Important

If you enabled the trace flags, make sure that you review the information in that article after you run the migration to SQL Server 2017 or SQL Server 2016. Many of the trace flags and configuration options that are listed in that article became default options in SQL Server 2017 and SQL Server 2016.

Table 1. Important updates and trace flags 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 Applicable version and build ranges column.

Note

  • Applicable version and build indicates the specific update in which the change or trace flag was introduced. If no CU is specified, all CUs in the SP are included.
  • Not applicable version and build indicates the specific update in which the change or trace flag became the default behavior. Therefore, just applying that update will be enough to receive the benefits.

Important

When you enable fixes that have trace flags in Always On environments, 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 Applicable version and build ranges Not applicable version and build ranges Knowledge Base article or blog link for more details
Heavy SOS_CACHESTORE spinlock contention or your plans are being evicted frequently on ad hoc query workloads. T174 SQL Server 2016 RTM to current SP/CU SQL Server 2017 RTM to current SP/CU None KB3026083 - FIX: SOS_CACHESTORE spinlock contention on ad hoc SQL Server plan cache causes high CPU usage in SQL Server
Entries in the plan cache are evicted because of growth in other caches or memory clerks High CPU consumption because of frequent recompiles of queries T8032 SQL Server 2016 RTM to current SP/CU SQL Server 2017 RTM to current SP/CU None Documentation of DBCC TRACEON - Trace Flags (Transact-SQL) See Cache Size Management section of Plan Cache Internals
tempdb is heavily used and has many modifications to the data in tempdb You encounter non-yielding scheduler messages when using Indirect Checkpoint for tempdb database T3468 SQL Server 2016 SP1 CU5 to current SP/CU SQL Server 2017 CU1 to current SP/CU None Indirect Checkpoint and tempdb - the good, the bad and the non-yielding scheduler KB4040276 - FIX: Indirect checkpoints on tempdb database cause "Non-yielding scheduler" error in SQL Server 2016 and 2017
Frequent short transactions happen in tempdbYou notice increased CPU usage for these transactions Common Criteria Compliance is not enabled T3427 SQL Server 2016 SP1 CU2 to SQL Server 2016 SP2 CU2 SQL Server 2017 RTM KB3216543 - FIX: Workloads that utilize many frequent, short transactions in SQL Server 2016 and 2017 may consume more CPU than in SQL Server 2014
You are troubleshooting specific query performance issues Optimizer fixes are disabled by default
T4199 SQL Server 2016 RTM to current SP/CU SQL Server 2017 RTM to current SP/CU None KB974006 - SQL Server query optimizer hotfix trace flag 4199 servicing model
Note Instead of the server level trace flag 4199, consider using database scoped option QUERY_OPTIMIZER_HOTFIXES or query hint ENABLE_QUERY_OPTIMIZER_HOTFIXES.
Statistics jobs take a long time to complete Cannot run multiple statistics update jobs in parallel T7471 SQL Server 2016 RTM CU1 to current SP/CU SQL Server 2017 RTM to current SP/CU None KB3156157 - Running multiple UPDATE STATISTICS for different statistics on a single table concurrently is available
Boosting Update Statistics performance with SQL 2014 & SQL 2016

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

Review the content in the Knowledge Base article or Books Online Resource column, and consider implementing the guidance in the Recommended actions column.

Knowledge Base article or Books Online resource Recommended actions
Configure the max degree of parallelism Server Configuration Option Use the sp_configure stored procedure to make configuration changes to Configure the max degree of parallelism Server Configuration Option for your instance of SQL Server per the Knowledge Base article.
Compute capacity limits by edition The license core limitation for SQL Server 2012 Enterprise Edition that has Server and Client Access License (CAL) licensing is limited to 20 cores per SQL Server instance.
There are no limits under the Core-based Server Licensing model.
Consider upgrading your edition of SQL Server to the appropriate SKU to use all hardware resources.
Slow performance on Windows Server when using the Balanced power plan Review the article, and work together with your Windows administrator to implement one of the solutions that are listed in the Resolution section of the article.
optimize for ad hoc workloads Server Configuration Option 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 discussed above, consider the optimize for ad hoc workloads server option and also the FORCED PARAMETERIZATION database option.
How to reduce paging of buffer pool memory in SQL Server
Memory configuration and sizing considerations in SQL Server 2012 and later versions
Assign the Enable the Lock Pages in Memory Option (Windows) user right to the SQL service Startup account. See How to enable the "locked pages" feature in SQL Server 2012. Set maximum server memory to approximately 90 percent of total physical memory. Make sure that the Server memory configuration options setting accounts for memory from only the nodes that are configured to use affinity mask settings.
SQL Server and Large Pages Explained... Tuning options for SQL Server when running in high performance workloads Consider enabling TF 834 if you have a server that has much memory, particularly for an analytical or data warehousing workload. Keep in mind that Interoperability of Columnstore indexes with large page memory model in SQL Server.
Query Performance issues associated with a large sized security cache How to customize the quota for the TokenAndPermUserStore cache store in SQL Server 2005 Service Pack 3 DBCC TRACEON - Trace Flags (Transact-SQL) If the security cache grows to a large size and you encounter performance problems and spinlock contention, consider enabling trace flag T4610 and T4618 to reduce the maximum size of TokenAndPermuserStore.
ALTER WORKLOAD GROUP KB3107401 - New query memory grant options are available (min_grant_percent and max_grant_percent) in SQL Server 2012 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. New query memory grant options are available (min_grant_percent and max_grant_percent) in SQL Server.
SQL 2016 - It Just Runs Faster: Automatic TEMPDB Configuration Add multiple data files of equal size for the tempdb database if this is an upgraded server. For new installs, setup automatically does this.
TEMPDB - Files and Trace Flags and Updates Use tempdb optimizations and improve scalability by avoiding or reducing DDL on temp objects
Instant File initialization Work together with your Windows administrator to grant the SQL Server service account the Perform Volume Maintenance Tasks user rights per the information in the Books Online topic.
Considerations for the "autogrow" and "autoshrink" settings in SQL Server Check the current settings of your database, and make sure that they are configured per the recommendations in the Knowledge Base article.
Indirect Checkpoints Consider enabling indirect checkpoints on user databases to optimize I/O behavior in SQL Server 2014 and 2012.
SQL Server : large RAM and DB Checkpointing Consider enabling indirect checkpoints on user databases to optimize I/O behavior in SQL Server 2014 and 2012.Review the required adjustments for tempdb in the reference Indirect Checkpoint and tempdb - the good, the bad and the non-yielding scheduler
KB3009974 - FIX: Slow synchronization when disks have different sector sizes for primary and secondary replica log files in SQL Server AG and Logshipping environments If you have an Availability Group in which the transaction log on the primary replica is on a disk that has a 512-byte sector size and the secondary replica transaction log is on a drive that has a 4-K sector size, you may experience slow synchronization. In this situation, enabling TF 1800 should correct the issue.
Query Profiling Infrastructure KB3170113 - 3170113 - Update to expose per-operator query execution statistics in showplan XML and Extended Event in SQL Server 2014 SP2 If your SQL Server is not already CPU bound and a 1.5 percent to 2 percent overhead is negligible for your workloads, we recommend you enable TF 7412 as a startup trace flag. This flag enables lightweight profiling in SQL Server 2014 SP2 or later. This lets you do live query troubleshooting in production environments.
Identify plan choice regressions using Query Store Activate the best query plan Use the query store feature to identify queries that regressed or are performing poorly If the query performance issues occur because of cardinality estimation, select the appropriate CE version: database-scoped option LEGACY_CARDINALITY_ESTIMATION, query hint LEGACY_CARDINALITY_ESTIMATION, database compatibility level, or trace flag 9481
Join containment assumption in the New Cardinality Estimator degrades query performance Evaluate the queries that use joins and filters to understand the effect of simple and base containment. Use trace flag 9476 for simple containment instead of base containment when you use the default cardinality estimator.
Improvements in compatibility level 130 Improvements in compatibility level 140 Use database compatibility level 130 or later to benefit from the following improvements:
Adaptive and aggressive threshold for updating existing statistics for larger tables
Better sampling and locking mechanism for statistics update
Statistics that are sampled by a multi-threaded process
Ability to run insert-select by using parallelismUse database compatibility level 140 or later to benefit from the following improvements:
Improved cardinality estimation and plan quality by using new features such as interleaved execution for multi-statement table-valued functions and adaptive join
Improved memory usage through memory grant feedback
Best Practice with the Query Store
Set capture mode to Auto
Enable trace flags 7745 and 7752 to improve performance of Query Store during High Availability and Disaster Recovery scenarios
Apply the fix in KB4340759 - FIX: Slow performance of SQL Server 2016 when Query Store is enabled if you experience query store spinlock contention under heavy workloads
SQL Server 2016/2017: Availability group secondary replica redo model and performance If you experience too many waits (PARALLEL_REDO_TRAN_TURN, DPT_ENTRY_LOCK or DIRTY_PAGE_TABLE_LOCK), review this blog to take corrective actions (apply applicable fix, evaluate appropriate use of redo model)
KB2634571 - Improvements for the DBCC CHECKDB command may result in faster performance when you use the PHYSICAL_ONLY option Improvements for the DBCC CHECKDB command may increase performance when you use the PHYSICAL_ONLY optionT2566 details in DBCC TRACEON - Trace Flags A faster CHECKDB - Part IV (SQL CLR UDTs) If you run DBCC CHECK commands on large databases (multiple TB sizes), consider using trace flags T2562, T2549, and T2566. Several checks are now located under the EXTENDED_LOGICAL_CHECK option in SQL Server 2016.
Protect SQL Server from attacks on Spectre and Meltdown side-channel vulnerabilities Carefully evaluate the performance of Kernel Virtual Address Shadowing (KVAS), Kernel Page Table Indirection (KPTI), and Indirect Branch Prediction mitigation (IBP) on various SQL Server workloads in your environment.

Table 3. Important changes that are introduced in SQL Server 2017 and SQL Server 2016

SQL Server 2017 and SQL Server 2016 contains several improvements in the areas of scalability and performance. Various configuration changes and trace flags that are required in SQL Server 2014 and SQL Server 2012 became the default behavior in SQL Server 2017 and 2016. This table provides an overview of all changes that are implemented in SQL Server 2017 and SQL Server 2016.

Area Summary of the change More information and references
SQL Engine Frequently used trace flags that are either retired or no longer needed in SQL Server 2016 and the later versions of SQL Server: 8048, 8079, 9024, 1236, 1118, 1117, 6498, 8075, 3449, 6532, 6533, 6534. SQL Server Trace Flags
Database Engine What's new in Database Engine - SQL Server 2017
Database Engine Breaking Changes to Database Engine Features in SQL Server 2016
Database Engine Breaking Changes to Database Engine Features in SQL Server 2017
Query Processing datatype conversions SQL Server 2016 (13.x) includes improvements in some data types conversions and some (mostly uncommon) operations. For more information, see SQL Server 2016 improvements in handling some data types and uncommon operations.
Availability Group For secondary database, initialization automatic seeding uses the database mirroring endpoints to stream the database content to the secondary and apply them. SQLSweet16!, Episode 2: Availability Groups Automatic Seeding
Availability Group SQL Server 2016 uses less context switches when it transports log blocks from primary to secondary. SQL 2016 - It Just Runs Faster: AlwaysOn Log Transport Reduced Context Switches SQL Server 2016 - It Just Runs Faster: Always On Availability Groups Turbocharged
Availability Group SQL Server 2016 uses improved compression algorithms and parallel compression of log block data. SQL 2016 - It Just Runs Faster: AlwaysOn Parallel Compression / Improved Algorithms
Availability Group SQL Server 2016 takes advantage of hardware that is based AES-NI encryption capabilities to improve Always On log shipping scalability and performance by a significant factor. SQL 2016 - It Just Runs Faster - AlwaysOn AES-NI Encryption
Performance SQL Server 2016 detects the CPU capabilities for AVX or SSE and uses the hardware-based vector capabilities to improve scalability and performance when compressing, building dictionaries, and processing columnstore data. SQL 2016 - It Just Runs Faster: Column Store Uses Vector Instructions (SSE/AVX)
Performance SQL Server 2016 takes advantage of CPU vector instructions to improve bulk insert performance. SQL 2016 - It Just Runs Faster - BULK INSERT Uses Vector Instructions (SSE/AVX)
Performance SQL Server 2016 enables an INSERT ... SELECT statement to operate by using parallelism significantly reducing the data loading time. SQLSweet16!, Episode 3: Parallel INSERT ... SELECT
Performance SQL Server 2016 enables TRUNCATE operation on individual partitions of a table for archiving older partitions. SQLSweet16!, Episode 5: TRUNCATE Selected Partitions
Performance SQL Server 2016 dynamically adjusts the size of the In-Memory Optimized Database worker pool as needed. SQL 2016 - It Just Runs Faster: In-Memory Optimized Database Worker Pool
Tempdb Allocations are tempdb and user databases uses uniform full extents. File growth in tempdb happens for all files at the same time. SQL 2016 - It Just Runs Faster: -T1117 and -T1118 changes for TEMPDB and user databases
Tempdb Database Engine setup automatically calculates the number of tempdb data files. SQL 2016 - It Just Runs Faster: Automatic TEMPDB Configuration
Storage Database engine uses 0xC0 stamp instead of 0x00 for transaction log file initialization. SQL 2016 - It Just Runs Faster: LDF Stamped
Storage For large memory servers and heavy write environments, indirect checkpoint performs better. SQL 2016 - It Just Runs Faster: Indirect Checkpoint Default
Storage High rates of transactions can benefit from multiple logwriters flushing log cache to transaction log. SQL 2016 - It Just Runs Faster: Multiple Log Writer Workers
Backup and Restore Backups can be compressed for database encrypted by using TDE if you specify MAXTRANSFERSIZE greater than 65536. SQLSweet16!, Episode 1: Backup Compression for TDE-enabled Databases
SQL OS Dynamically partition memory objects to reduce memory object contention. SQL 2016 - It Just Runs Faster: Dynamic Memory Object (CMemThread) Partitioning
SQL OS SQL Server 2016 monitors the quantum usage patterns of workers allowing all workers to get fair treatment and improve scalability. SQL 2016 - It Just Runs Faster: Updated Scheduling Algorithms
SQL OS SQL Server 2016 interrogates the hardware layout and automatically configures Soft NUMA on systems reporting 8 or more CPUs per NUMA node. The partitioning triggers various adjustments throughout the database engine for improved scalability and performance. SQL 2016 - It Just Runs Faster: Automatic Soft NUMA
DBCC CHECK Specify MAXDOP to manage resources that are consumed by the DBCC CHECK command. SQLSweet16!, Episode 6: DBCC CHECKDB with MAXDOP
DBCC CHECK DBCC CHECK uses an improved page scanning algorithm that has less contention and advanced read-ahead capabilities. SQL 2016 - It Just Runs Faster: DBCC Scales 7x Better
DBCC CHECK DBCC CHECK commands take a long time when SQL Server evaluates special data types and indexes. These checks moved under EXTENDED_LOGICAL_CHECKS option. SQL 2016 - It Just Runs Faster: DBCC Extended Checks
Code page BULK INSERT or bcp utilities improved to load UTF-8 data into a table in SQL Server. SQLSweet16!, Episode 10: "I can eat glass ...", but can I load it into a database?
Spatial SQL Server 2016 removes the PInvoke and PUnInvoke activities during T-SQL execution for many of the spatial methods. SQL 2016 - It Just Runs Faster: Native Spatial Implementation(s)
Spatial SQL Server 2016 improves the scalability of TVP that uses spatial data by using native spatial validations. SQL 2016 - It Just Runs Faster: TVPs with Spatial Column(s)
Spatial The native and TVP spatial improvements enable SQL Server to optimize index creation and tessellation of spatial data. SQL 2016 - It Just Runs Faster: Spatial Index Builds Faster
MSDTC SQL Server 2016 dynamically starts MSDTC as needed allowing resources to be used for other activities until required. SQL 2016 - Leverages On Demand MSDTC Startup
XEvent Various changes are made to the XEvent Linq provider logic to reduce context switching, memory allocations, and other aspects for faster rendering of events. SQL 2016 - It Just Runs Faster: XEvent Linq Reader

Table 4. Important fixes that are included in a CU

Review the description in the Symptoms column and apply the required updates (preferably the latest update that contains the specific fix) in the Required update column in applicable environments. You can review the Knowledge Base article for more information about the respective issues. These recommendations do not require you to enable additional trace flags as startup parameters unless it is explicitly called out in the article or in this table. Just applying the latest CU or Service Pack that includes these fixes is enough to get the benefit.

Note The CU name in the Required update column provides the first CU of SQL Server that resolves this issue. A cumulative update contains all the hotfixes and all the updates that were included together with the previous SQL Server update release. As noted in the updates to the SQL Server Incremental Servicing Model, we recommend that you install the latest cumulative update in an ongoing proactive cadence to resolve or prevent the issues that are described. Also note that starting in SQL Server 2017, the Modern Servicing Model for SQL Server was introduced so that service packs are no longer made available.

Applicable version Area or component Description of issue addressed Recommended update
SQL Server 2016SQL Server 2017 Backup & Restore Log backup of a TDE-enabled database fails and returns error 33111 intermittently when looking for an older copy of certificate that was used to encrypt the DEK in the past if non-default MAXTRANSFERSIZE is used FIX: Errors 33111 and 3013 when backing up TDE-encrypted database in SQL Server
Cumulative Update 2 for SQL Server 2017
Cumulative Update 6 for SQL Server 2016 SP1
Cumulative Update 9 for SQL Server 2016
SQL Server 2016SQL Server 2017 Backup & Restore RESTORE HEADERONLY statement for a TDE compressed backup slow to complete in SQL Server FIX: RESTORE HEADERONLY statement for a TDE compressed backup slow to complete in SQL Server
Cumulative Update 8 for SQL Server 2017
Cumulative Update 1 for SQL Server 2016 SP2
SQL Server 2016 Backup & Restore Fail to compress the backup file when INIT and COMPRESSION option is used in a TDE-enabled database FIX: Fail to compress the backup file when INIT and COMPRESSION option is used in a TDE-enabled database in SQL Server 2016
Cumulative Update 7 for SQL Server 2016 RTM
CU 4 for SQL Server 2016 SP1
SQL Server 2016 Backup & Restore Assertion failure when backing up large TDE encrypted database in SQL Server
Cumulative Update 4 for SQL Server 2016 SP1
SQL Server 2016 Backup & Restore Restore fails when you do backup by using compression and checksum on a TDE-enabled database FIX: Restore fails when you do backup by using compression and checksum on a TDE-enabled database in SQL Server 2016
Cumulative Update 7 for SQL Server 2016 RTM
Cumulative Update 4 for SQL Server 2016 SP1
SQL Server 2016 Backup & Restore Error 9004 when you try to restore a compressed backup from multiple files for a large TDE-encrypted database in SQL Server FIX: Error 9004 when you try to restore a compressed backup from multiple files for a large TDE-encrypted
Cumulative Update 7 for SQL Server 2016 RTM
SQL Server 2016SQL Server 2017 Backup & Restore Slow restore performance when you restore a backup by using compression on a 4-K sector in SQL Server KB4088193 - FIX: Slow restore performance when restoring a compressed backup on a disk with 4K sector size in SQL Server
Cumulative update 9 for SQL Server 2016 SP1
Cumulative Update 1 for SQL Server 2016 SP2
Cumulative Update 7 for SQL Server 2017
SQL Server 2016SQL Server 2017 Backup & Restore [VDI] Restore of a TDE-compressed backup is unsuccessful when using the VDI client Cumulative Update 7 for SQL Server 2017
Cumulative Update 1 for SQL Server 2016 SP2
Cumulative Update 9 for SQL Server 2016 SP1
SQL Server 2016SQL Server 2017 Backup & Restore [VDI] Restoring a backup-compressed, TDE-enabled database through the VDI interface fails and returns OS Error 38
Cumulative Update 8 for SQL Server 2017 [VSTS Bug # 10936552]
SQL Server 2016 SP2 RTM [VSTS Bug # 10698847]
SQL Server 2016SQL Server 2017 Backup & Restore [VSS] Backup of availability database through a VSS-based application may fail in SQL Server FIX: Backup of availability database via VSS-based application may fail in SQL Server
Cumulative Update 1 for SQL Server 2017
Cumulative Update 9 for SQL Server 2016 RTM
Cumulative Update 5 for SQL Server 2016 SP1
Cumulative Update 8 for SQL Server 2014 SP2
SQL Server 2016SQL Server 2017 Backup & Restore TDE-enabled backup and restore are slow if the encryption key is stored in an EKM provider in SQL Server TDE-enabled backup and restore slow if encryption key is stored in EKM
Cumulative Update 8 for SQL Server 2017
Cumulative Update 1 for SQL Server 2016 Service Pack 2
Cumulative Update 9 for SQL Server 2016 Service Pack 1
SQL Server 2016SQL Server 2017 Always On AG Columnstore Queries that retrieve data by using non-clustered index seek take longer FIX: Data retrieval queries using non-clustered index seek take much longer in SQL Server
Cumulative Update 2 for SQL Server 2017
Cumulative Update 6 for SQL Server 2016 Service Pack 1
Cumulative Update 9 for SQL Server 2016
SQL Server 2016SQL Server 2017 Always On AG Parallel redo in a secondary replica of an Availability Group that contains heap tables generates a runtime assert dump or the server that is running SQL Server crashes and returns an access violation error FIX: Parallel redo in a secondary replica of an availability group that contains heap tables generates a runtime assert dump or the SQL Server crashes with an access violation error
Cumulative update 9 for SQL Server 2016 SP1
Cumulative Update 1 for SQL Server 2016 SP2
Cumulative Update 6 for SQL Server 2017
SQL Server 2016 Always On AG Assertion occurs when you use parallel redo in a secondary replica of a SQL Server AlwaysOn Availability Group FIX: Assertion occurs when you use parallel redo in a secondary replica of a SQL Server AlwaysOn Availability Group
Cumulative Update 3 for SQL Server 2016
SQL Server 2016SQL Server 2017 Always On AG Performance is slow for an Always On AG when you process a read query FIX: Always On AG slow when processing read query in SQL Server
Cumulative Update 8 for SQL Server 2017
Cumulative Update 1 for SQL Server 2016 SP2
Cumulative Update 9 for SQL Server 2016 SP1
SQL Server 2017 Always On AG Improvement to reduce the failover duration for an Availability Group in SQL Server on Linux Improvement to reduce the failover duration for an availability group in SQL Server on Linux
Cumulative Update 8 for SQL Server 2017
SQL Server 2017 Always On AG Improvements for Always On Availability Groups on a Pacemaker cluster in SQL Server Improvements for Always On Availability Groups on a Pacemaker cluster in SQL Server
Cumulative Update 8 for SQL Server 2017
SQL Server 2016 Memory Parallel redo causes high memory usage FIX: Parallel redo causes high memory usage in SQL Server 2016 when it's compared to SQL Server 2014 or earlier versions
Cumulative Update 3 for SQL Server 2016 SP1
SQL Server 2016SQL Server 2017 Memory sp_execute_external_script and DMV sys.dm_exec_cached_plans cause memory leaks FIX: System stored procedure sp_execute_external_script and DMV sys.dm_exec_cached_plans cause memory leaks in SQL Server 2017 and 2016
Cumulative Update 4 for SQL Server 2017
Cumulative update 8 for SQL Server 2016 SP1
SQL Server 2016SQL Server 2017 Memory Out-of-memory error when the virtual address space of the SQL Server process is low Out of memory error when the virtual address space of the SQL Server process is low in SQL Server
Cumulative Update 4 for SQL Server 2017
Cumulative update 8 for SQL Server 2016 SP1
SQL Server 2016 Memory memory leak occurs when you use Azure Storage in SQL Server A memory leak occurs when you use Azure Storage in SQL Server 2014 or 2016
Cumulative Update 5 for SQL Server 2016 RTM
Cumulative Update 2 for SQL Server 2016 SP1
Cumulative Update 2 for SQL Server 2016
SQL Server 2016SQL Server 2017 In-Memory OLTP Large disk checkpoint usage occurs for an In-Memory-optimized filegroup FIX: Large disk checkpoint usage occurs for an In-Memory optimized filegroup during heavy non-In-Memory workloads
Cumulative Update 6 for SQL Server 2017
Cumulative update 8 for SQL Server 2016 SP1
Cumulative Update 1 for SQL Server 2016
SQL Server 2016 In-Memory OLTP Checkpoint files grow excessively when you insert data into memory-optimized tables FIX: Checkpoint files grow excessively when you insert data into memory-optimized tables in SQL Server 2016
Cumulative Update 2 for SQL Server 2016 SP1
Cumulative Update 4 for SQL Server 2016
SQL Server 2016SQL Server 2017 In-Memory OLTP Recovery of database takes a long time when it contains memory-optimized tables Recovering a database that has memory-optimized tables takes a long time in SQL Server 2017 and 2016
Cumulative Update 4 for SQL Server 2017
Cumulative Update 7 for SQL Server 2016 SP1
SQL Server 2016SQL Server 2017 tempdb PFS page round robin algorithm improvement PFS page round robin algorithm improvement in SQL Server 2016
Cumulative Update 7 for SQL Server 2017
Cumulative Update 1 for SQL Server 2016 SP2
Cumulative Update 9 for SQL Server 2016 SP1
SQL Server 2016SQL Server 2017 tempdb Performance issues occur in the form of PAGELATCH_EX and PAGELATCH_SH waits in TempDB(sys.sysobjvalues and sys.sysseobjvalues) Performance issues occur in form of PAGELATCH_EX and PAGELATCH_SH waits
Cumulative Update 1 for SQL Server 2016 Service Pack 2
Cumulative Update 9 for SQL Server 2016 Service Pack 1
SQL Server 2016SQL Server 2017 tempdb Heavy tempdb contentionTemp table alter that has named constraints requires a synchronous drop of the temp table Heavy tempdb contention occurs in SQL Server 2016 or 2017
Cumulative Update 5 for SQL Server 2017
Cumulative update 8 for SQL Server 2016 SP1
SQL Server 2017 tempdb PAGELATCH_EX contentions when you remove temporary objects (sys.sysobjvalues) FIX: PAGELATCH_EX contentions when removing temporary objects in SQL Server
Cumulative Update 5 for SQL Server 2017
SQL Server 2016 tempdb Increased PAGELATCH_EX contentions in sys.sysobjvalues FIX: Increased PAGELATCH_EX contentions in sys.sysobjvalues in SQL Server 2016
Cumulative Update 6 for SQL Server 2016 RTM
Cumulative Update 2 for SQL Server 2016 Service Pack 1
SQL Server 2016SQL Server 2017 tempdb Indirect checkpoints on tempdb database cause "Non-yielding scheduler" error FIX: Indirect checkpoints on the tempdb database cause "Non-yielding scheduler" error in SQL Server 2017 and 2016
Cumulative Update 1 for SQL Server 2017
Cumulative Update 5 for SQL Server 2016 Service Pack 1
Cumulative Update 8 for SQL Server 2016
SQL Server 2016SQL Server 2017 tempdb Workloads that use many frequent, short transactions may consume more CPU Workloads that utilize many frequent, short transactions in SQL Server 2017 and 2016 may consume more CPU than in SQL Server 2014
Cumulative Update 4 for SQL Server 2017
Cumulative Update 2 for SQL Server 2016 SP1
SQL Server 2016SQL Server 2017 Transaction log Error 9002 when there is no sufficient disk space for critical log growth KB4087406 - FIX: Error 9002 when there is no sufficient disk space for critical log growth in SQL Server 2014, 2016, and 2017
Cumulative Update 5 for SQL Server 2017
Cumulative Update 1 for SQL Server 2016 SP2
Cumulative update 8 for SQL Server 2016 SP1
Cumulative Update 11 for SQL Server 2014 SP2
SQL Server 2016 Security Cache High CPU usage causes performance issues in SQL Server 2016 High spinlock contention for SECURITY_CACHE and CMED_HASH_SET SQLSweet16!, Episode 8: How SQL Server 2016 Cumulative Update 2 (CU2) can improve performance of highly concurrent workloads KB3195888 - FIX: High CPU usage causes performance issues in SQL Server 2016 and 2017
Cumulative Update 2 for SQL Server 2016
SQL Server 2017 Query Store Access violation occurs when Query Store collects runtime statistics Access violation when Query Store collects runtime statistics in SQL Server 2017
Cumulative Update 5 for SQL Server 2017
SQL Server 2016 Query Store Query Store automatic data cleanup fails on editions other than Enterprise and Developer edition Query Store automatic data cleanup fails on editions other than Enterprise and Developer edition of SQL Server 2016
Cumulative Update 1 for SQL Server 2016
SQL Server 2016 Query Store Slow performance of SQL Server when Query Store is enabled KB4340759 - FIX: Slow performance of SQL Server 2016 when Query Store is enabled
Cumulative Update 2 for SQL Server 2016 SP2

This table is a compilation of all key improvements, recommendations, and code changes that were released in cumulative updates after SQL Server 2017 was released. Review the description in the Symptoms column, and apply the required updates (preferably the latest update that contains the specific fix) in the Required update column in applicable environments. You can review the listed Knowledge Base article for more information about the respective issues.

These recommendations do not require you to enable additional trace flags as startup parameters unless it is explicitly called out in the article or in this table. Just applying the latest cumulative update or service pack that includes these fixes is enough to get the benefit. If you are using AlwaysOn Availability Group in SQL Server on Linux, please upgrade SQL Server 2017 to Cumulative Update 8 or higher since several improvements were delivered in this update. 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. As noted in the updates to the SQL Server Incremental Servicing Model, we now recommend that you install the latest cumulative update in an ongoing proactive cadence to resolve or prevent the issues that are described. Also note that starting with SQL Server 2017, the Modern Servicing Model for SQL Server was introduced so that service packs are no longer made available.

Summary of change or improvement More information and references
SQL and OS: Review the various best practices recommendations for the OS and SQL Server when deploying SQL Server on Linux Performance best practices and configuration guidelines for SQL Server on Linux
SQL Agent Improvement: SQL Server Agent jobs can start without waiting for all databases to recover SQL Server Agent jobs can start without waiting for all databases to recover in SQL Server 2017 on Linux
Cumulative Update 9 for SQL Server 2017
Storage Improvement: Enable "forced flush" mechanism in SQL Server 2017 Enable forced flush mechanism in SQL Server 2017 on Linux
Cumulative Update 6 for SQL Server 2017
Storage Improvement: Move master database and error log file to another location KB4053439 - Improvement: Move master database and error log file to another location in SQL Server 2017 on Linux
Cumulative Update 4 for SQL Server 2017
AG Improvement: Improvements for Always On Availability Groups on a Pacemaker cluster in SQL Server KB4339875 - Improvements for Always On Availability Groups on a Pacemaker cluster in SQL Server
Cumulative Update 8 for SQL Server 2017
Memory Improvement: Minimum memory limit set to 2 GB to install or start SQL Server KB4052969 - FIX: Minimum memory limit set to 2 GB to install or start SQL Server 2017
Cumulative Update 2 for SQL Server 2017
Memory FIX: Memory ramp-up phase is too long after TF 834 is enabled KB4075203 - FIX: Memory ramp-up phase is too long after TF 834 is enabled in SQL Server 2017 on Linux
Cumulative Update 4 for SQL Server 2017
Scheduling FIX: Portability and performance differ between Windows and Linux scheduler mappings in SQL Server 2017 KB4043455 - FIX: Portability and performance differ between Windows and Linux scheduler mappings in SQL Server 2017
Cumulative Update 1 for SQL Server 2017
AD Auth FIX: Can't create a login based on a user that belongs to the parent domain KB4073670 - FIX: Can't create a login based on a user that belongs to the parent domain in SQL Server 2017 on Linux
Cumulative Update 4 for SQL Server 2017
AD Auth Update: Improves SQL server performance by restricting which KDC can be contacted in geographically large networks KB4463314 - Update improves SQL server performance by restricting which KDC can be contacted in geographically large networks
Cumulative Update 11 for SQL Server 2017
AD Auth FIX: SQL Server crashes when you use third-party Active Directory providers KB4466962 - FIX: SQL Server 2017 crashes when you use third-party Active Directory providers
Cumulative Update 12 for SQL Server 2017
TSQL FIX: NEWSEQUENTIALID function generates duplicate GUID after SQL Server is restarted KB4078097 - FIX: NEWSEQUENTIALID function generates duplicate GUID after SQL Server 2017 on Linux is restarted
Cumulative update 4 for SQL Server 2017
Connections FIX: Unexpected memory consumption when TCP protocol connections used KB4073045 - FIX: Unexpected memory consumption when TCP protocol connections are used for SQL Server 2017 on Linux
Cumulative Update 4 for SQL Server 2017
Connections FIX: Name resolution error occurs when IPv6 is disabled on startup KB4053392 - FIX: Name resolution error occurs when IPv6 is disabled on startup in SQL Server 2017 on Linux
Cumulative Update 2 for SQL Server 2017
Connections FIX: SQL Server doesn't listen to nondefault IP address specified by mssql-conf script KB4053393 - FIX: SQL Server 2017 on Linux doesn't listen to nondefault IP address specified by mssql-conf script
Cumulative Update 2 for SQL Server 2017
Setup FIX: script upgrade failures when applying CU Errors when upgrading SQL Server 2017 CU4 or later and not enabling SQL Agent on Linux
Cumulative Update 6 for SQL Server 2017
Database Mail FIX: Database Mail cannot connect to SQL Server when non-default TCP port is used KB4100873 - FIX: Database Mail cannot connect to SQL Server 2017 on Linux when non-default TCP port is used
Cumulative Update 6 for SQL Server 2017
Container FIX: Can't stop the SQL Server Linux Docker container by using the "docker stop" command KB4093805 - FIX: Can't stop the SQL Server Linux Docker container by using the “docker stop” command
Cumulative Update 5 for SQL Server 2017
Container FIX: Out of memory error when you run SQL Server in a Linux Docker container KB4347055 - FIX: Out of memory error when you run SQL Server 2017 in a Linux Docker container
Cumulative Update 10 for SQL Server 2017
AG: If you are using Pacemaker package 1.1.18-11.el7 or higher, adjust the start-failure-is-fatal property KB4229789 - Cumulative Update 7 for SQL Server 2017
See the Pacemaker Notice section
AG FIX: Pacemaker may kill the resource agent processes when operation times out KB4460203 - FIX: Pacemaker may kill the resource agent processes when operation times out in SQL Server 2017 AlwaysOn Availability Group
Cumulative Update 11 for SQL Server 2017
AG FIX: Two SQL Server instances are the primary replica of an availability group KB4316791 - FIX: Two SQL Server instances are the primary replica of an availability group in SQL Server
Cumulative Update 8 for SQL Server 2017
AG FIX: Unnecessary failover when you use AlwaysOn Availability Group KB4056922 - FIX: Unnecessary failover when you use AlwaysOn Availability Group in SQL Server 2017 on Linux
Cumulative Update 3 for SQL Server 2017
AG FIX: Pacemaker demotes existing primary replica of an AlwaysOn AG and never promotes a new one KB4076982 - FIX: Pacemaker demotes existing primary replica of an AlwaysOn AG in SQL Server 2017 on Linux and never promotes a new one
Cumulative update 4 for SQL Server 2017
AG FIX: Pacemaker promotes an unsynchronized replica to primary when you use AlwaysOn AG KB4091722 - FIX: Pacemaker promotes an unsynchronized replica to primary when you use AlwaysOn AG in SQL Server 2017 on Linux
Cumulative Update 5 for SQL Server 2017
AG FIX: Pacemaker promotion of local replica to primary fails when using AlwaysOn AG KB4230542 - FIX: Pacemaker promotion of local replica to primary fails when using AlwaysOn AG in SQL Server 2017
Cumulative Update 7 for SQL Server 2017
AG FIX: Startup of a database that belongs to an availability group times out KB4316790 - FIX: Startup of a database that belongs to an availability group times out in SQL Server on Linux
Cumulative Update 8 for SQL Server 2017
AG FIX: Unnecessary failovers occur when a SQL Server Failover Cluster Instance or Always On AG is managed by Pacemaker KB4316793 - FIX: Unnecessary failovers occur when a SQL Server 2017 Failover Cluster Instance or Always On AG is managed by Pacemaker
Cumulative Update 8 for SQL Server 2017