SQL Server 2016 Service Pack 2 (SP2) released !!!

  • Article

The SQL Server team is excited to bring you the second service pack release for SQL Server 2016! The SQL Server 2016 Service Pack 2 (SP2) contains a rollup of released hotfixes as well as multiple improvements centered around performance, scalability, and supportability based on feedback from customers and the SQL community. These improvements enable SQL Server 2016 to perform faster and with expanded supportability and diagnostics. It also showcases the SQL Server product team’s commitment to provide continued value into in-market releases.

SQL Server 2016 Service Pack 2 includes:

  • Performance and scale improvements for SQL Server, such as improved database backup performance on large memory machines and added backup compression support, which helps performance of almost all databases.
  • Supportability and diagnostics enhancements, such as improved troubleshooting and additional information about statistics used during query plan optimization.
  • New improvements based on Connect feedback items filed by the SQL Server Community.
  • Some improvements originally introduced in SQL Server 2014 SP2 and SQL Server 2012 SP4.
  • All the fixes up to SQL Server 2016 SP1 CU8, including the security update for "speculative execution side-channel attacks".

Following is the detailed list of improvements introduced in SQL Server 2016 SP2:

Performance and Scale

  1. Improved Distribution DB cleanup procedure – An oversized distribution database tables caused blocking and deadlock situation. An improved cleanup procedure aims to eliminate some of these blocking or deadlock scenarios.
  2. Change Tracking Cleanup – Improved change tracking cleanup performance and efficiency for Change Tracking side tables.
  3. Support for cancelling request through setting CPU time out in Resource Governor – Improves the handling of query requests by actually cancelling the request, if CPU thresholds for a request is reached. This behavior is enabled under trace flag 2422.
  4. SELECT INTO to create target table in the desired filegroup – Starting SQL Server 2016 SP2, SELECT INTO T-SQL syntax supports loading a table into a filegroup other than a default filegroup of the user using the ON <Filegroup name> keyword in T-SQL syntax.
  5. Improved Indirect Checkpoint for TempDB – Indirect checkpointing for TempDB is improved to minimize the spinlock contention on DPLists. This improvement allows TempDB workload on SQL Server 2016 to scale out of the box if indirect checkpointing is ON for TempDB.
  6. Improved database backup performance on large memory machines – SQL Server 2016 SP2 optimizes the way we drain the on-going I/O during backup resulting in dramatic gains in backup performance for small to medium databases. We have seen more than 100x improvement when taking system database backups on a 2TB machine. The performance gain reduces as the database size increases as the pages to backup and backup I/O takes more time compared to iterating buffer pool. This improvement will help improve the backup performance for customers hosting multiple small databases on a large high end servers with large memory.
  7. VDI backup compression support for TDE enabled databases – SQL Server 2016 SP2, adds VDI support to allow VDI backup solutions to leverage compression for TDE enabled databases. With this improvement, a new backup format has been introduced to support backup compression for TDE enabled databases. The SQL Server engine will transparently handle new and old backup formats to restore the backups.
  8. Dynamic loading of replication agent profile parameters – This new enhancements allows replication agents parameters to be loaded dynamically without having to restart the agent. This change is applicable only to the most commonly used agent profile parameters.
  9. Support MAXDOP option for statistics create/update – This enhancement allows to specify the MAXDOP option for a CREATE/UPDATE statistics statement, as well as make sure the right MAXDOP setting is used when statistics are updated as part of create or rebuild for all types of indexes (if the MAXDOP option is present)
  10. Improved Auto Statistics Update for Incremental Statistics – In certain scenarios, when a number of data changes happened across multiple partitions in a table in a way that the total modification counter for incremented statistics exceeds the auto update threshold, but none of the individual partitions exceed the auto update threshold, statistics update may be delayed until much more modifications happen in the table. This behavior is corrected under trace flag 11024.

Supportability and Diagnostics

  1. Full DTC support for databases in an Availability Group –  Cross-databases transactions for databases which are part of an Availability Group are currently not supported for SQL Server 2016. With SQL Server 2016 SP2, we are introducing full support for distributed transactions with Availability Group Databases.
  2. Update to sys.databases is_encrypted column to accurately reflect encryption status for TempDB – The value of is_encryptedcolumn column in sys.databases is 1 for TempDB, even after you turn off encryption for all user databases and restart SQL Server. The expected behavior would be that the value for this is 0, since TempDB is no longer encrypted in this situation. Starting with SQL Server 2016 SP2, sys.databases.is_encrypted now accurately reflects encryption status for TempDB.
  3. New DBCC CLONEDATABASE options to generate verified clone and backup – With SQL Server 2016 SP2, DBCC CLONEDATABASE allows two new options:  produce a verified clone, or produce a backup clone. When a clone database is created using WITH VERIFY_CLONEDB option, a consistent database clone is created and verified which will be supported by Microsoft for production use. A new property is introduced to validate if the clone is verified SELECT DATABASEPROPERTYEX('clone_database_name', 'IsVerifiedClone') . When a clone is created with BACKUP_CLONEDB option, a backup is generated in the same folder as the data file to make it easy for customers to move the clone to different server or to send it to Microsoft Customer Support (CSS) for troubleshooting.
  4. Service Broker (SSB) support for DBCC CLONEDATABASE – Enhanced DBCC CLONEDATABASE command to allow scripting of SSB objects.
  5. New DMV to monitor TempDB version store space usage – A new sys.dm_tran_version_store_space_usage DMV is introduced in SQL Server 2016 SP2 to allow monitoring TempDB for version store usage. DBAs can now proactively plan TempDB sizing based on the version store usage requirement per database, without any performance overhead when running it on production servers.
  6. Full Dumps support for Replication Agents - Today if replication agents encounter a unhandled exception, the default is to create a mini dump of the exception symptoms. This makes troubleshooting unhandled exception issues very difficult. Through this change we are introducing a new Registry key, which would allow to create a full dump for Replication Agents.
  7. Extended Events enhancement for read routing failure for an Availability Group – Before, the read_only_rout_fail xEvent fired if there was a routing list present, but none of the servers in the routing list were available for connections. SQL Server 2016 SP2 includes additional information to assist with troubleshooting, and also expand on the code points where this xEvent gets fired.
  8. New DMVs to monitor transaction log information – Two new transaction log DMVs are introduced in SQL Server 2016 SP2: sys.dm_db_log_stats and sys.dm_db_log_info. These DMVs can be used to monitor, alert and avert potential T-Log issues experienced by customers.
    1. sys.dm_db_log_stats returns summary level information about the log.
    2. sys.dm_db_log_info exposes the VLF information similar to DBCC LOGINFO.
  9. Processor Information in sys.dm_os_sys_info – New columns added to the sys.dm_os_sys_info DMV to expose the processor related information, such as socket_count, and cores_per_numa.
  10. Extent modified information in sys.dm_db_file_space_usage –  New column added to sys.dm_db_file_space_usage to track the number of modified extents since the last full backup.
  11. Segment information in sys.dm_exec_query_stats – New columns were added to sys.dm_exec_query_stats to track number of columnstore segments skipped and read, such as total_columnstore_segment_reads, and total_columnstore_segment_skips.
  12. Setting correct compatibility level for distribution database – After Service Pack installation, the Distribution database compatibility level changes to 90. This was because of an code path in sp_vupgrade_replication stored procedure. The SP has now been changed to set the correct compatibility level for the distribution database.
  13. Expose last known good DBCC CHECKDB information – A new database option has been added to programmatically return the date of the last successful DBCC CHECKDB run. Users can now query DATABASEPROPERTYEX([database], 'lastgoodcheckdbtime') to obtain a single value representing the date/time of the last successful DBCC CHECKDB run on the specified database.
  14. Showplan XML enhancements include:
    1. Information on which statistics were used to compile the query plan, including statistics name, modification counter, sampling percent, and when the statistics was updated last time. Note this is added for CE models 120 and later only, i.e. is not supported for CE 70.
    2. A new attribute EstimateRowsWithoutRowgoal is added to showplan XML if Query Optimizer uses “row goal” logic.
    3. New runtime attributes UdfCpuTime and UdfElapsedTime in actual showplan XML , to track time spent in scalar User-Defined Functions (UDF).
    4. Add CXPACKET wait type to list of possible top 10 waits in actual showplan XML – Parallel query execution frequently involves CXPACKET waits, but this type of wait was not reporting in actual showplan XML.
    5. Extended the runtime spill warning to report number of pages written to TempDB during a parallelism operator spill.
  15. Replication Support for databases with Supplemental characters collations – Replication is now supportable on databases which use the Supplemental Character Collation.
  16. Proper handling of Service Broker with Availability group failover – In the current implementation when Service Broker is enabled on an Availability Group Databases, during an AG failover all Service broker connections which originated on the Primary Replica are left open. This improvement targets to close all such open connections during an AG failover.
  17. Improved parallelism waits troubleshooting by adding a new CXCONSUMER wait.
  18. Improved consistency between DMVs for same information – The sys.dm_exec_session_wait_stats DMV now tracks CXPACKET and CXCONSUMER waits consistently with the sys.dm_os_wait_stats DMV.
  19. Improved troubleshooting of intra-query parallelism deadlocks. This improvements includes:
    1. A new exchange_spill Extended Event to report the number of pages written to TempDB during a parallelism operator spill, in the xEvent field name worktable_physical_writes.
    2. The spills columns in the sys.dm_exec_query_stats, sys.dm_exec_procedure_stats, and sys.dm_exec_trigger_stats DMVs (such as total_spills) now also include the data spilled by parallelism operators.
    3. The XML deadlock graph is improved for parallelism deadlock scenarios, with more attributes added to the exchangeEvent resource.
    4. The XML deadlock graph is improved for deadlocks involving batch-mode operators, with more attributes added to the SyncPoint resource.
  20. Dynamic reloading of some replication agent profile parameters – In the current implementation of replication agents any change in the agent profile parameter requires the agent to be stopped and restarted. This improvements allows for the parameters to be dynamically reloaded without having to restart the replication agent.

Note that all the newly introduced Trace flags with SQL Server 2016 SP2 are documented and can be found at https://aka.ms/traceflags.

The SQL Server 2016 SP2 installation may require reboot after installation. As a best practice, we recommend to plan and perform a reboot following the installation of SQL Server 2016 SP2.

We will be updating and adding follow-up posts on the Tiger blog in the coming weeks to describe some of the above improvements in detail.

As noted above, SP2 contains a roll-up of solutions provided in SQL Server 2016 cumulative updates up to, and including the latest Cumulative Update 8 for Service Pack 1 – SP1 CU8, and the security update for "speculative execution side-channel attacks" released on January 2*nd 2018.* Therefore, there is no reason to wait for a future SP2 CU1 to ‘catch–up‘ with SQL Server 2016 SP1 CU8 content.

The service pack is now available for download on the Microsoft Download Center and Microsoft Update, and will be coming soon to Visual Studio Subscriptions, MBS/Partner Source, and VLSC. As part of our commitment to software excellence for our customers, this upgrade is available to all customers with existing SQL Server 2016 deployments.

Find more information on SQL Server 2016 SP2 in our KB article and Release Notes, and download today by visiting the links below:

Thank you,
Microsoft SQL Server Engineering Team