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.
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: Use database compatibility level 140 or later to benefit from the following improvements: |
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.
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 Always On 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 Always On Availability Group | KB4056922 - FIX: Unnecessary failover when you use Always On Availability Group in SQL Server 2017 on Linux Cumulative Update 3 for SQL Server 2017 |
AG FIX: Pacemaker demotes existing primary replica of an Always On AG and never promotes a new one | KB4076982 - FIX: Pacemaker demotes existing primary replica of an Always On 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 Always On AG | KB4091722 - FIX: Pacemaker promotes an unsynchronized replica to primary when you use Always On 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 Always On AG | KB4230542 - FIX: Pacemaker promotion of local replica to primary fails when using Always On 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 |