Performance and Stability Related Fixes in Post-SQL Server 2016 SP2 Builds

As of December 9, 2019, there have been eleven Cumulative Updates (CU) for the Service Pack 2 branch of SQL Server 2016. There have been a large number of hotfixes in each of these cumulative updates. If you are running on the SQL Server 2016 SP2 branch (which you should be by now), I really think you should be running the latest SQL Server 2016 SP2 Cumulative Update.

Table 1 shows the SQL Server 2016 SP2 CU builds that have been released so far:

Build	Description	Release Date
13.0.5149	SP2 CU1	May 30, 2018
13.0.5153	SP2 CU2	July 16, 2018
13.0.5216	SP2 CU3	September 20, 2018
13.0.5233	SP2 CU4	November 13, 2018
13.0.5264	SP2 CU5	January 23, 2019
13.0.5292	SP2 CU6	March 19, 2019
13.0.5337	SP2 CU7	May 22, 2019
13.0.5426	SP2 CU8	July 31, 2019
13.0.5492	SP2 CU10	October 8, 2019
13.0.5598	SP2 CU11	December 9, 2019

Table 1: SQL Server 2016 SP2 CU Builds

You can follow the KB article link below to see all of the CU builds for the SQL Server 2016 RTM, SQL Server 2016 SP1 and SQL Server 2016 SP2 branches.

SQL Server 2016 Build Versions

Like I have done for other versions and branches of SQL Server, I decided to scan the hotfix list for all of the Cumulative Updates in the SP2 branch, looking for performance and general reliability-related fixes for the SQL Server Database Engine. I came up with the list below, but this listing is completely arbitrary on my part. You may come up with a completely different list, based on what specific SQL Server 2016 features you are using.

Here are the fixes in the SP2 branch:

SQL Server 2016 SP2 Cumulative Update 1 (Build 13.0.5149), 29 total public hot fixes

Performance issues occur in the form of PAGELATCH_EX and PAGELATCH_SH waits in TempDB when you use SQL Server 2016

FIX: Error 9002 when there is no sufficient disk space for critical log growth in SQL Server 2014, 2016, and 2017

FIX: “Cannot use SAVE TRANSACTION within a distributed transaction” error when you execute a stored procedure in SQL Server

PFS page round robin algorithm improvement in SQL Server 2016

FIX: A memory assertion failure occurs and the server is unable to make any new connections in SQL Server

FIX: One worker thread seems to hang after another worker thread is aborted when you run a parallel query in SQL Server

FIX: TDE enabled database backup with compression causes database corruption in SQL Server 2016

FIX: Restore of a TDE compressed backup is unsuccessful when using the VDI client

FIX: Performance is slow for an Always On AG when you process a read query in SQL Server

FIX: Floating point overflow error occurs when you execute a nested natively compiled module that uses EXP functions in SQL Server

FIX: Database cannot be dropped after its storage is disconnected and reconnected in SQL Server

FIX: TDE database goes offline during log flush operations when connectivity issues cause the EKM provider to become inaccessible in SQL Server

FIX: TDE-enabled backup and restore operations are slow if the encryption key is stored in an EKM provider in SQL Server

FIX: Access violation occurs when you query a table with an integer column in SQL Server 2017 and SQL Server 2016

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

FIX: An assertion failure occurs when you execute a nested select query against a columnstore index in SQL Server

FIX: RESTORE HEADERONLY statement for a TDE compressed backup takes a long time to complete in SQL Server

FIX: An access violation occurs when incremental statistics are automatically updated on a table in SQL Server

 

SQL Server 2016 SP2 Cumulative Update 2 (Build 13.0.5153), 21 total public hot fixes

FIX: Many xml_deadlock_report events are reported for one single intra-query deadlock occurrence in SQL Server 2016

FIX: An instance of SQL Server may appear unresponsive then a “Non-yielding Scheduler” error may occur in SQL Server 2016

FIX: Parallel redo does not work after you disable Trace Flag 3459 in an instance of SQL Server

Improvement: Configure SESSION_TIMEOUT value for a Distributed Availability Group replica in SQL Server 2016 and 2017

FIX: Slow performance of SQL Server 2016 when Query Store is enabled

FIX: “PAGE_FAULT_IN_NONPAGED_AREA” Stop error when enumerating contents in a SQL Server FileTable directory

Transparent Data Encryption added for Log Shipping in SQL Server

FIX: Query on a secondary replica takes two times as long to run as on a primary replica in SQL Server

FIX: “Corrupted index” message and server disconnection when an update statistics query uses hash aggregate on SQL Server

FIX: DMVs sys.dm_db_log_stats and sys.dm_db_log_info may return incorrect values for the last database of the SQL Server 2016 instance

FIX: VSS backup fails in secondary replica of Basic Availability Groups in SQL Server 2016

FIX: TDE enabled database backup with compression causes database corruption in SQL Server

FIX: Error 19432 when you use Always On Availability Groups in SQL Server

FIX: Event notifications for AUDIT_LOGIN and AUDIT_LOGIN_FAILED events will cause an unusual growth of TempDB in SQL Server 2016

 

 SQL Server 2016 SP2 Cumulative Update 3 (Build 13.0.5216), 28 total public hot fixes

FIX: Leakage of sensitive data occurs when you enable DDM function in SQL Server 2016 and 2017

FIX: Out of memory error occurs even when there are many free pages in SQL Server

Improvement: Update to add spill information of batch-mode operators to “Warnings” section of Showplan XML in SQL Server 2016

FIX: A memory leak occurs in sqlwepxxx.dll causes the WmiPrvSe.exe process to crash

FIX: Assertion error occurs when you use sys.dm_exec_query_statistics_xml in SQL Server 2016

FIX: The SQL Service shuts down after SP2 is installed on SQL Server 2016 with c2 audit mode enabled

FIX: Transaction delays on the primary replica if database synchronization is reported incorrectly on a secondary replica in SQL Server

Update adds lightweight query profiling hint in SQL Server 2016

FIX: Error 41317 when you enable server audit and you use in-memory transactions in SQL Server

FIX: Access violation in cross data center failover if you use Always On Availability Groups in SQL Server

FIX: Slow query performance occurs when you use NULL filters on Partition Key with default CE in SQL Server 2016

FIX: Upgrading SP1 instance fails when you use SxS instances of SQL Server 2016 SP1 and SP2

FIX: Poor performance occurs when you run a query against columnstore in an RCSI-enabled database in SQL Server 2016

FIX: Workloads that utilize many frequent, short transactions in SQL Server 2016 and 2017 may consume more CPU than in SQL Server 2014

 

 SQL Server 2016 SP2 Cumulative Update 4 (Build 13.0.5233), 36 total public hot fixes

FIX: Access violation occurs when SQL Server 2016 tries to start Query Store Manager during startup

FIX: Access violation when you try to access a table when page compression is enabled on the table in SQL Server

FIX: SQL Server may generate EXCEPTION_ACCESS_VIOLATION dump file when you merge two partitions of system-versioned temporal tables in SQL Server 2016

FIX: “9003 error, sev 20, state 1” error when a backup operation fails on a secondary replica that is running under asynchronous-commit mode

FIX: Overestimations when using default Cardinality Estimator to query table with many null values

FIX: Query operation freezes when you insert data into a clustered columnstore index in parallel in SQL Server data warehousing

FIX: Access violation occurs in compile code when you parse the forced plan in SQL Server 2017

FIX: The “modification_counter” in DMV sys.dm_db_stats_properties shows incorrect value when partitions are merged through ALTER PARTITION in SQL Server 2016

FIX: Assertion error occurs when you run a MERGE statement with an OUTPUT clause in SQL Server 2017

FIX: “ran out of memory” error when executing a query on a table that has a large full-text index in SQL Server 2014

FIX: Backing up a SQL Server 2008 database by using a VSS backup application may fail after installing CU10 for SQL Server 2017

FIX: Assertion error occurs when you restart the SQL Server 2016 database

FIX: Query plans are different on clone database created by DBCC CLONEDATABASE and its original database in SQL Server 2016

FIX: Access violation occurs when you query data from a view created on a table with columnstore index in SQL Server 2016

FIX: Excessive memory usage when you trace RPC events that involve Table-Valued Parameters in SQL Server 2017

FIX: Access violation occurs in Distribution Agent in SQL Server 2017

FIX: Assertion occurs when you use parallel redo in a secondary replica of SQL Server 2016 AG

FIX: DefaultLanguage.LCID property changes for partially contained In-Memory OLTP database

FIX: Assertion error occurs during restoration of TDE compressed backups in SQL Server 2016

FIX: Error 3961 occurs when you use Application roles for the second time in read-only secondary replicas in SQL Server 2016 AG

FIX: Access violation when you run a granular audit policy for DML in SQL Server

 

 SQL Server 2016 SP2 Cumulative Update 5 (Build 13.0.5264), 43 total public hot fixes

FIX: Access violation when you compile a query and histogram amendment is enabled with default Cardinality Estimation in SQL Server 2016 and 2017

FIX: Access violations and unhandled exceptions when you set automatic seeding for secondary replica or Distributed Availability Group replica in SQL Server

FIX: Masked data is exposed when a query that uses sp_cursorfetch is run in SQL Server if Dynamic Data Masking is enabled

FIX: Access violation when you run a query that uses the XML data type in SQL Server 2014, 2016 and 2017

FIX: Transactions and log truncation may be blocked when you use Query Store in SQL Server 2016 and 2017

FIX: Error occurs when the Database Encryption Key is longer than 3,456 bits in SQL Server 2016 and 2017

FIX: I/O error on a BPE file causes buffer time-out in SQL Server

FIX: Out of memory error occurs when Database Node Memory (KB) drops below 2 percent in SQL Server 2014 and 2016

FIX: “Non-yielding” error occurs when there is a heavy use of prepared statements in SQL Server 2014 and 2016

FIX: Restore of TDE-compressed backup is unsuccessful when backing up database to a 512-byte Emulation disk in SQL Server

FIX: SQL Server installation fails if one of the remote nodes is unreachable in a cluster

FIX: High CPU usage when there are many batch requests in SQL Server 2016

FIX: SQL Server service crashes when DBCC CHECKDB runs against a database that has a corrupted partition

FIX: Server crashes when you cancel DBCC CHECKDB against a large database in SQL Server 2016

FIX: Columnstore Index build request may time out after 25 seconds though the memory grant time-out is configured in SQL Server 2016

FIX: ObjectPropertyEx returns incorrect row count when there are partitions in a database object

FIX: sys.fn_hadr_backup_is_preferred_replica returns TRUE for more than one secondary replica even if the priority values are identical in SQL Server 2016

FIX: Restore or Restore Verifyonly of a TDE-compressed backup fails with error 33111 and 3013 in SQL Server 2016 and 2017

FIX: Access violation occurs and server stops unexpectedly when you use XEvent session with sqlos.wait_info event in SQL Server 2016

FIX: Possible assertion failure when a cross-database transaction involving an Availability Group database is committed from a SQL Server trigger

Snapshot Isolation and Savepoint support added for Availability Group databases on the same instance with DTC enabled in SQL Server

FIX: Access violation when you run a query that contains a batch-mode hash join on Clustered Columnstore Index table in SQL Server 2016

FIX: Upgrade to SQL Server 2016 SP2 CU3 or CU4 fails with an error when sysadmin account “sa” is renamed in SQL Server 2016

FIX: Access violation occurs when SQL is trying to promote a transaction to DTC while collecting QDS statistics

FIX: Assertion failure occurs when you try to back up database in limited disk space in SQL Server 2016

FIX: Query Store enabled database takes long time on startup after you apply cumulative update for a SQL Server version

 

 SQL Server 2016 SP2 Cumulative Update 6 (Build 13.0.5292), 26 total public hot fixes

FIX: Non-yielding scheduler issue occurs when you run queries in batch-mode that spill in SQL Server 2016

Improvement: Optional replacement for “String or binary data would be truncated” message with extended information in SQL Server 2016 and 2017

FIX: SQL Writer Service can cause undetected deadlocks on system DMV when you do a VSS backup

FIX: Users are incorrectly permitted to create incremental statistics on nonclustered indexes which are not aligned to the base table in SQL Server 2016

FIX: Assertion occurs when a parallel query deletes from a Filestream table in SQL Server 2014 and 2016

FIX: FILESTREAM for file I/O access feature can’t be enabled when you use Cluster Shared Volumes (CSV) in SQL Server 2016 Failover Cluster Instances

FIX: Assertion occurs when linked server which points to itself is used in a cross-database transaction in SQL Server 2016

FIX: Error 10314 occurs when you load .NET CLR assembly in SQL Server 2016 database

FIX: Filtered NCI over a CCI may not be maintained when the table is updated in a way that none of the key or included columns of the NCI are changed

FIX: Stack Dump occurs in the change tracking cleanup process in SQL Server 2016

Improvement: DMV sys.dm_hadr_cluster reports cloud witness quorum type “4” and quorum_type_desc “UNKNOWN_QUORUM” in SQL Server 2016

FIX: Automatic seeding assertions when databases are removed from AG in SQL Server 2016

FIX: Error occurs when sp_addarticle is used to add article for transactional replication to memory-optimized table on subscriber in SQL Server 2016

FIX: A specially crafted query run by a low privileged user may expose the masked data in SQL Server 2016

FIX: Error occurs when you back up a virtual machine with non-component based backup in SQL Server 2016

 

 SQL Server 2016 SP2 Cumulative Update 7 (Build 13.0.5337), 27 total public hot fixes

FIX: Repl_Schema_Access wait issues when there are multiple publisher databases on the same instance of SQL Server 2016

FIX: Filtered index may be corrupted when you rebuild index in parallel in SQL Server 2014 and 2016

FIX: Assertion error occurs when you use sys.dm_exec_query_statistics_xml in SQL Server 2016

FIX: Access violation occurs when you query sys.dm_hadr_availability_replica_states in SQL Server 2016

FIX: Floating point exception error 3628 occurs when you query DMV sys.dm_db_xtp_hash_index_stats in SQL Server 2016

FIX: Manual failover between forwarder and secondary replica fails with all replicas synchronized in SQL Server 2016

FIX: Error occurs when you back up a virtual machine with non-component based backup in SQL Server 2016

FIX: Access violation occurs when you run a query against sys.availability_replicas in SQL Server 2016

FIX: Adding new Publication may fail if distribution database is in AG and collation is set to BIN in SQL Server 2016

FIX: Query against table with both clustered columnstore index and nonclustered rowstore index may return incorrect results in SQL Server 2016

FIX: Fail to join the secondary replica if the database has a defunct filegroup in SQL Server 2014 and 2016

FIX: Columnstore filter pushdown may return wrong results when there is an overflow in filter expressions in SQL Server 2014 and 2016

FIX: Indirect checkpoints on tempdb database cause “Non-yielding scheduler” error in SQL Server 2016

FIX: Log reader agent may fail after AG failover with TF 1448 enabled in SQL Server 2014 and 2016

FIX: Tlog grows quickly when you run auto cleanup procedure in SQL Server 2016

FIX: AG is suspended when cross-database transaction is applied on AG databases in SQL Server 2016

Fix: “Non-yielding Scheduler” occurs when you clean up in-memory runtime statistics of Query Store in SQL Server 2016

FIX: Data movement to DAG Forwarder does not resume automatically after connection time-out in SQL Server 2016

FIX: SQL Server 2016 does not perform the requested pre-row assignments when you use MERGE statement that performs assignments of local variables for each row

FIX: Access violation occurs when you run sys.fn_dump_dblog function in SQL Server 2016

FIX: Assertion error occurs when you run a query to access sys.dm_db_xtp_checkpoint_files in SQL Server 2016

 

 SQL Server 2016 SP2 Cumulative Update 8 (Build 13.0.5427), 28 total public hot fixes

FIX: Encryption scan task triggered upon the startup of a database may be canceled in SQL Server 2016

FIX: Peer-to-peer replication fails in SQL Server 2016 if the host name is not uppercase

FIX: “No valid credentials provided” occurs after you restart PolyBase in SQL Server 2016 and 2017

FIX: Assertion dump occurs when you select a view on a linked server in SQL Server 2016 and 2017

Improvement: Allow altering external data source in APS in SQL Server 2016

FIX: Concurrent Inserts into a CCI can cause deadlock under memory pressure in SQL Server 2016

FIX: Intermittent “row not found” error at Azure SQL DB Subscriber caused by duplication of BEGIN TRAN statements

FIX: Prolonged non-transactional usage of FileTable without instance restart may cause non-yielding scheduler error or server crash in SQL Server 2014, 2016 and 2017

FIX: DBCC SHOW_STATISTICS permission check fails with an AV error in SQL Server 2016 and 2017

FIX: Filled transaction log causes outages when you run Query Store in SQL Server 2016 and 2017

FIX: Access violation error occurs when SQL Server 2016 uses hash join, hash aggregate, sort or window function in batch-mode plan in the deadlock monitor

FIX: Access violation occurs when you enable TF 3924 to clean orphaned DTC transactions in SQL Server 2016

FIX: Memory dumps generate when access violation occurs in SQL Server 2016

FIX: Error 409 occurs when you back up databases by using BackuptoURL

FIX: Syscommittab cleanup causes a lock escalation that will block the syscommittab flush in SQL Server 2016

FIX: Data masking may not be applied when you use PIVOT or UNPIVOT function on masked column in SQL Server 2016 and 2017

FIX: Access Violation occurs when you use LOG function with a remote query in SQL Server 2016 or 2017

FIX: Assertion error occurs when SQL Server 2016 uses hash join, hash aggregate, sort or window function in batch-mode plan

FIX: “Non-yielding Scheduler” occurs when you run queries with batch-mode hash join and/or sort in SQL Server 2016

FIX: Querying sys.tables returns temporary tables created by concurrent users starting from SQL Server 2012

FIX: Restore or RESTORE VERIFYONLY of a TDE-compressed backup fails in SQL Server 2016 and 2017

FIX: Azure storage I/O subsystem may not reset transfer details for a failed I/O resulting in backup errors

FIX: Internal thread deadlock occurs on secondary replica of availability group in SQL Server 2016 SP2 and 2017

FIX: Error 9003 occurs when you back up a database on a secondary replica in Microsoft SQL Server 2016

 

 SQL Server 2016 SP2 Cumulative Update 10 (Build 13.0.5492), 21 total public hot fixes

FIX: Transaction log isn‘t truncated on a single node Availability Group in SQL Server 2016

FIX: Orphaned CLR sessions cause blocking in SQL Server 2016

FIX: Access violation occurs when you run queries that involve PIVOT or UNPIVOT in SQL Server 2016

FIX: Incorrect results occur with index intersection on partitioned table with a clustered columnstore index in SQL Server 2016

FIX: Access violation occurs when you enable TF 3924 to clean orphaned DTC transactions in SQL Server

FIX: Stack dump occurs when table type has a user-defined constraint in SQL Server 2016

FIX: When you change SEEDING_MODE to MANUAL using ALTER AVAILABILITY GROUP, SQL Server 2016 does not cancel the current seeding operations to the secondary replica

FIX: SQL Writer Service fails to back up in non-component backup path in SQL Server 2016

FIX: Cardinality property for a table doesn’t account for the rows in the delta store or the deleted bitmap if the table has a clustered columnstore index

FIX: Access violation occurs when a clone database verification fails in SQL Server 2016

FIX: You may receive wrong results when you query sys.database_scoped_configurations in SQL Server 2016

FIX: Poor query performance due to low cardinality estimation in SQL Server 2016 when you use default CE and column is covered by both single and multi column statistics

FIX: SQL Server 2016 database remains in frozen I/O state indefinitely when backed up by VSS

 

 SQL Server 2016 SP2 Cumulative Update 11 (Build 13.0.5492), 26 total public hot fixes

FIX: Error 41168 occurs when you try to alter DAG SEEDING_MODE in SQL Server 2016 and 2017

FIX: Self-deadlock occurs when transaction auditing is enabled in SQL Server 2016 and 2017

FIX: Non-yielding scheduler condition occurs if large number of row-column values are processed in row groups in SQL Server 2016

FIX: Exception error 3628 may occur when you execute stored procedure in SQL Server 2016 and 2017

FIX: Database cannot recover and reports error 5243 in SQL Server 2016 and 2017

FIX: UPDATE STATISTICS takes very long time to generate maintenance plan for large databases in SQL Server 2016 and 2017

FIX: You may encounter a non-yielding scheduler condition when you run query with parallel batch-mode sort operator in SQL Server 2016

FIX: DBCC CHECKDB with EXTENDED_LOGICAL_CHECKS fails on a table in SQL Server 2016

FIX: Unable to restore SQL Server 2012 databases on SQL Server 2016 because of NCCI

FIX: Error 8959 may occur on IAM page when you query sys.dm_db_index_physical_stats against partitioned columnstore table after partition switch in SQL Server

FIX: Access violation occurs when you restore the In-Memory Optimized database in SQL Server 2016 and 2017

FIX: Assertion error occurs when you use IDENT_CURRENT on view that has identity columns in SQL Server 2016

FIX: Restore fails when you try to restore compressed TDE backups prior to SQL Server 2016 SP2 CU4 on SQL Server 2016 SP2 CU8

FIX: Access violation occurs when you use sys.dm_os_memory_objects in SQL Server 2016 and 2017

FIX: Error 8601 occurs when you run a query with partition function in SQL Server

FIX: Scripts generated by SSMS collect different wait types after you install SQL Server 2016 SP2 CU10

FIX: Non-yielding scheduler issue occurs in SQL Server 2016 when you run an online build for an index that is not partition-aligned

FIX: “The File location cannot be opened” error occurs when you try to open a FileTable directory in SQL Server

FIX: CREATE INDEX with new CE reads the partition table and results in huge row count higher than than the total table row count in SQL Server 2016

FIX: Assertion error occurs when you run getHKTable function to get Hekaton database in SQL Server 2016

 

The reason that I put these lists together is that I want to convince more people to try to keep their SQL Server instances up to date with Cumulative Updates. If you do the proper testing, planning and preparation, I think the risks from installing a SQL Server Cumulative Update are quite low (despite the occasional issues that people run into).

If you install a Cumulative Update or Service Pack on a Production system the day it is released, after doing no testing whatsoever, and then run into problems (and don’t have a plan on how to recover), then I don’t have that much sympathy for you.

On the other hand, if you go through a thoughtful and thorough testing process, and you have a plan for how you will install the CU, and how you would recover if there were any problems, then you are much less likely to have any problems. You are also much more likely to avoid the issues that are fixed by all of the included fixes in the new build of SQL Server. You have done your job as a good DBA.

Finally, Microsoft has changed their official guidance about whether you should install SQL Server Cumulative Updates. As they say, “we now recommend ongoing, proactive installation of CU’s as they become available”.