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

Article
06/20/2023

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.

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 other performance tuning.
Table 3 contains information about changes in behavior and default settings in SQL 2017 and 2016.
Table 4 contains other 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 tempdb. You notice increased CPU usage for these transactions Common Criteria Compliance isn't 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're 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. Can't 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...	Consider enabling Trace Flag 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 Queries take a longer time to finish running when the size of the TokenAndPermUserStore cache grows in SQL Server 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're 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. For more information, see Trace Flag 1800.
Query Profiling Infrastructure KB3170113 -Update to expose per-operator query execution statistics in showplan XML and Extended Event in SQL Server 2014 SP2	If your SQL Server isn't 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 parallelism Use 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 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: Always On 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: Always On 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 - Always On 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 don't require you to enable other trace flags as startup parameters unless it's 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 Always On Availability Group	FIX: Assertion occurs when you use parallel redo in a secondary replica of a SQL Server Always On 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

Table 5: Recommended improvements, fixes, and configuration guidelines for SQL Server in a Linux environment

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 don't require you to enable other trace flags as startup parameters unless it's 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're using Always On Availability Group in SQL Server on Linux, 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.

Share via