SQL Server 2016 SP2: Cumulative Update 13 (KB4549825) Now Available

Table of Contents

Cumulative Update 13 for SQL Server 2016 Service Pack 2 (SP2) is now available, bringing a collection of critical fixes and improvements to enhance the performance, stability, and reliability of your SQL Server environment. This update addresses a range of issues reported by users and identified internally, spanning various SQL Server components. Applying this cumulative update is recommended to ensure your SQL Server 2016 SP2 instances are running with the most up-to-date and robust code base. This document outlines the key fixes included in this release, categorized by SQL Server component area.

Key Fixes and Improvements in Cumulative Update 13¶

This cumulative update contains resolutions for a variety of issues across different SQL Server functionalities. These fixes are aimed at improving the overall user experience and addressing potential problems that could impact database operations. Below is a detailed breakdown of the fixes included in this update, categorized for clarity.

SQL Engine Enhancements¶

This cumulative update includes several important fixes for the SQL Engine, addressing issues related to data integrity, query processing, and overall engine stability. These enhancements are crucial for maintaining a healthy and performant database environment.

Fix for Access Violation Exception with sp_server_diagnostics (KB13395275)¶

An access violation exception, a critical error that can lead to application instability, has been resolved when executing the sp_server_diagnostics stored procedure. This procedure is commonly used for diagnosing server health and performance, and this fix ensures its reliable operation without causing unexpected exceptions. Users relying on sp_server_diagnostics for monitoring and troubleshooting will benefit from this improvement.

Fix for Incorrect Corruption Report by DBCC CHECKDB on Spatial Index (KB13418764)¶

The DBCC CHECKDB command, a vital tool for database integrity checks, could incorrectly report corruption on spatial indexes if the base table contained a column named “Id”. This false positive could lead to unnecessary investigations and concerns. This cumulative update rectifies this issue, ensuring that DBCC CHECKDB accurately assesses the integrity of spatial indexes, providing reliable database health reporting.

Improvement for Memory Dump Generation Speed (Filtered Dumps) (KB13433228)¶

The speed of memory dump generation, specifically filtered dumps, has been significantly improved by leveraging the Page Exclusion Bitmap mechanism. PageExclusionBitmap is enabled by default in SQL Server 2016. Faster memory dump generation is crucial for quicker problem diagnosis and resolution when critical issues occur, minimizing downtime and accelerating recovery processes. Filtered dumps, in particular, help to reduce the size of dump files, making them easier to manage and analyze.

Fix for Access Violation Exception during Latch Promotion (KB4551720) (KB13435878)¶

An access violation exception could occur during latch promotion of frequently used database pages in SQL Server 2016 and 2017. Latches are lightweight locking mechanisms, and issues with their promotion can lead to performance bottlenecks or even crashes. This fix resolves this access violation, enhancing the stability of the SQL Server engine under heavy workloads and concurrent access to database pages.

Fix for Access Violation Exception with Read Uncommitted Mode and XML Data Types (KB13443327)¶

Executing queries in read uncommitted mode with high concurrent read or write patterns over XML data types could trigger an access violation exception. Read uncommitted isolation level, while offering performance benefits, can sometimes introduce concurrency-related issues. This fix addresses this specific scenario, ensuring stability when dealing with XML data types under high concurrency in read uncommitted mode.

Fix for Deadlock during Concurrent Subdirectory Creation in FileTable (KB13457594)¶

When concurrently creating subdirectories within a FileTable directory, an internal deadlock within the SQL Server Engine could occur. This deadlock could prevent subsequent requests to FileTable directories and files from responding. FileTable provides a way to manage files within SQL Server, and this fix ensures the robustness of FileTable operations, especially in scenarios involving concurrent directory creation.

Fix for DELETE Statement Foreign Key Check Constraint Error (KB13488608)¶

A false foreign key check constraint error could be returned by a DELETE statement even when the referenced table had no matching rows. This error, “Msg 547,” incorrectly indicated a conflict with a foreign key constraint when no actual conflict existed. This fix resolves this issue, ensuring accurate foreign key constraint checks and preventing erroneous error messages during delete operations.

Fix for Non-yielding Messages with Large Number of Expressions (KB13418158)¶

Users could encounter non-yielding messages (Msg 17883) when compiling queries with a large number of expressions. These messages indicate that a process is not releasing the scheduler, potentially leading to performance degradation or server unresponsiveness. This cumulative update addresses this issue, improving the handling of complex queries with numerous expressions and preventing non-yielding scheduler conditions.

Fix for sys.key_constraints Reporting Duplicate Rows for XML Indexes (KB13502076)¶

The sys.key_constraints system view could incorrectly report duplicate rows for an index if an XML component ID shared the same ID as the object_id of the primary key. System views provide metadata about database objects, and accurate reporting is crucial for database administration and management tools. This fix ensures that sys.key_constraints provides correct and non-duplicated information regarding indexes, particularly those involving XML components.

Fix for Error with DBCC CHECKTABLE/CHECKFILEGROUP/CHECKDB on CCI in Read-Only Filegroup (KB13507493)¶

Executing DBCC CHECKTABLE, CHECKFILEGROUP, or CHECKDB commands against a database with a table containing a Clustered Columnstore Index (CCI) located on a read-only filegroup could result in error message “Msg 8921”. This error indicated a failure during fact collection, possibly due to tempdb space issues or system table inconsistencies. This fix resolves this problem, allowing these DBCC commands to function correctly even when dealing with CCIs on read-only filegroups, ensuring comprehensive database integrity checks.

FIX: Distributed Transactions May Experience Long Waits with DTC_STATE Wait Type (KB4560183) (KB13508254)¶

Distributed transactions could experience prolonged waits with the DTC_STATE wait type. Distributed Transaction Coordinator (DTC) is responsible for managing transactions across multiple databases or systems. Long waits in DTC can significantly impact application performance. This fix addresses the underlying cause of these long waits, improving the performance and responsiveness of distributed transactions.

Fix for Assertion Exception or Access Violation in sys.dm_db_file_space_usage DMV (KB13517385)¶

Querying the sys.dm_db_file_space_usage Dynamic Management View (DMV) could lead to assertion exceptions or access violations. DMVs are essential for monitoring and diagnosing SQL Server performance and resource usage. This fix resolves these exceptions, ensuring the reliable operation of sys.dm_db_file_space_usage and enabling accurate monitoring of database file space usage.

FIX: Concurrent Inserts Against Tables with Columnstore Indexes May Cause Queries to Hang (KB4561305) (KB13517428)¶

Concurrent inserts against tables with columnstore indexes could cause queries to hang. Columnstore indexes are designed for high-performance data warehousing and analytical workloads. Hangs during concurrent inserts can severely impact real-time data ingestion and query performance. This fix addresses this concurrency issue, improving the performance and responsiveness of columnstore indexes under concurrent insert workloads.

Fix for Assertion on Mirror Server during Redo Process (KB13520595)¶

An assertion could occur on the mirror server during the redo process in SQL Server 2016. Database mirroring provides high availability and disaster recovery capabilities. Assertions during redo can disrupt mirroring operations and potentially lead to failover issues. This fix resolves this assertion, enhancing the reliability of database mirroring and ensuring smoother failover processes. The assertion message typically included: “Assertion: File: <FileName>, line = <LineNumber> Failed Assertion = ‘result == LCK_OK’“.

Fix for Access Violation Exception with Recursive CTE and Clustered Columnstore Index (CCI) (KB13525856)¶

Executing a query with a recursive Common Table Expression (CTE) whose anchor member referenced a Clustered Columnstore Index (CCI) could result in an access violation exception. Recursive CTEs are used for hierarchical data queries, and CCIs are used for analytical workloads. This fix resolves this specific interaction between recursive CTEs and CCIs, ensuring stable query execution in these scenarios.

Fix for Access Violation Exception with Non-existing Partition Function in Query (KB13530802)¶

An access violation exception could occur when executing a query that referenced a non-existent partition function. Partition functions are used for dividing tables into smaller, more manageable pieces. This fix prevents access violation exceptions when queries incorrectly reference partition functions that do not exist, improving error handling and query robustness.

FIX: Assertion Dump May Occur When Implicit Transactions Are Enabled (KB4563597) (KB13525672)¶

Assertion dumps could occur when implicit transactions were enabled in SQL Server 2016 and 2017. Implicit transactions manage transaction boundaries automatically. Assertion dumps are critical errors that can lead to server instability. This fix resolves this issue related to implicit transactions, enhancing the stability of SQL Server when using this transaction mode.

Fix for SQL Server Startup Failure Due to Script Level Downgrade Issue (KB13545688)¶

SQL Server could fail to start with an error indicating that the script level for ‘system_xevents_modification.sql’ in the ‘master’ database could not be downgraded. This issue typically arose after attaching a database from a newer SQL Server version. This fix addresses this startup failure scenario, allowing SQL Server to start correctly even after encountering databases from newer versions (though downgrading databases is generally not supported, this fix addresses a specific startup issue).

Fix for Spatial Data Type (Geometry/Geography) Schema Change Error (KB13458569)¶

Spatial data types (Geometry and Geography), implemented as CLR data types, could cause spatial queries to fail with schema change errors. This issue occurred when the application domain hosting spatial data type structures was unloaded, which the engine incorrectly interpreted as a schema change. This fix ensures proper handling of spatial data types and prevents schema change errors in these scenarios, improving the reliability of spatial queries. This fix applies to all supported platforms (All).

Reporting Services Updates¶

This cumulative update addresses a critical issue in Reporting Services, ensuring consistent and correct URL handling.

FIX: SSRS 2016 URLs are Case-Sensitive After Applying Security Update GDRs (KB4556096) (KB13422835)¶

After applying Security Update GDRs KB4532097 or KB4535706, SSRS 2016 URLs became case-sensitive. This change in behavior could break existing applications and user workflows that relied on case-insensitive URLs. This cumulative update reverts this behavior, ensuring that SSRS 2016 URLs remain case-insensitive as expected, maintaining compatibility and preventing disruptions.

Setup & Install Improvements¶

This cumulative update includes fixes related to the setup and installation process, particularly concerning LocalDB and R Setup components.

Fix for LocalDB Binary Update Failure During SP/CU/GDR Application (KB13431656)¶

When applying Service Packs (SP), Cumulative Updates (CU), or General Distribution Releases (GDR) on SQL Server 2016 with LocalDB installed, the update of LocalDB binaries might fail on the first attempt. However, a second attempt would typically succeed. This fix resolves this intermittent failure, ensuring that LocalDB binaries are consistently updated during the first application of SP/CU/GDR, streamlining the update process.

R Setup Component TLS 1.2 Support (KB13491308, KB13503406)¶

The R Setup components could fail to download CAB files when TLS 1.0 was disabled. This cumulative update includes a new R Setup version that adds support for TLS 1.2. TLS 1.2 is a more secure protocol than TLS 1.0, and disabling older protocols is a security best practice. This update ensures that R Setup components can function correctly in environments where TLS 1.0 is disabled and TLS 1.2 is used, enhancing security and enabling the use of R integration features.

High Availability Improvements¶

This cumulative update includes fixes that enhance the reliability and stability of high availability features like Always On Availability Groups and database mirroring.

FIX: Upgrade Script May Fail When Using Always On Secondary Replica (KB4563115) (KB13487253)¶

Upgrade scripts could fail if an Always On high availability group was used as a secondary replica in SQL Server 2016. Upgrading SQL Server in Always On environments requires careful coordination and can be complex. This fix addresses a potential failure point during upgrades involving secondary replicas in Always On, improving the robustness of the upgrade process in high availability configurations.

FIX: Full-Text Search Auto Crawl Stops When Availability Group Goes Offline (KB4511771) (KB13485749)¶

Full-Text search auto crawl could stop when an Availability Group went offline in SQL Server 2014 and 2016. Full-Text search indexing needs to be maintained even during availability group failovers. This fix ensures that Full-Text search auto crawl continues to function correctly and resume after an availability group goes offline and comes back online, maintaining the freshness of Full-Text indexes in high availability environments.

Fix for Replication Error ID Value Increase (KB13478331)¶

When a replication error such as a deadlock occurred, the ID value in the MSRepl_errors table could increase by a large amount instead of incrementing by only 1 as expected. The MSRepl_errors table logs replication errors, and correct ID sequencing is important for tracking and managing these errors. This fix ensures that the ID value in MSRepl_errors increments correctly, providing accurate and sequential error logging for replication troubleshooting.

Integration Services Updates¶

This update addresses a performance issue in Integration Services related to the TransferSqlServerObjectsTask.

Fix for Long Package Execution Time in TransferSqlServerObjectsTask (KB13463169)¶

Package execution times involving the SSIS task of type TransferSqlServerObjectsTask could be excessively long when the database contained tens of thousands of tables and the database user was not db_owner. TransferSqlServerObjectsTask is used to transfer database objects between SQL Server instances. This fix optimizes the performance of this task in scenarios with large numbers of tables and non-db_owner users, significantly reducing package execution times.

SQL Performance Improvements¶

This cumulative update includes performance improvements and fixes for specific performance-related issues.

Improved MDX Query Performance for Ragged Hierarchies in SSAS Multidimensional (KB13490149)¶

This update improves MDX query execution performance against dimension user hierarchies that are ragged hierarchies (HideMemberIf property set) and have deep hierarchy levels in SQL Server Analysis Services (SSAS) Multidimensional instances. Ragged hierarchies are common in dimensional modeling, and performance improvements in MDX query execution are crucial for faster data analysis and reporting in SSAS Multidimensional environments.

Fix for ALTER Command Rollback Triggering Prematurely (KB13482849)¶

When running ALTER commands with the ROLLBACK IMMEDIATE option, the rollback could trigger before the command was fully processed, even if the ALTER itself failed due to permission issues. This behavior could lead to unexpected rollback actions. This hotfix ensures that the rollback is processed only after the ALTER command completes, regardless of whether the ALTER command succeeds or fails, providing more predictable and controlled transaction behavior.

Conclusion¶

Cumulative Update 13 for SQL Server 2016 SP2 delivers a significant set of fixes and enhancements across various SQL Server components. Addressing issues ranging from access violation exceptions and data corruption reports to performance bottlenecks and setup failures, this update is highly recommended for all users of SQL Server 2016 SP2. Applying this cumulative update will contribute to a more stable, reliable, and performant SQL Server environment.

We encourage you to review the complete list of fixes and consider applying Cumulative Update 13 for SQL Server 2016 SP2 to enhance the stability, performance, and security of your SQL Server environment. Share your experiences and questions in the comments below!