Microsoft’s SQL Server team has announced the availability of the second service pack for SQL Server 2016. As usual, the update comes with a rollup of hotfixes and several improvements. The company says the service pack focuses on boosting performance, supportability, and scalability.

In its release notes, Microsoft says it once again on user feedback from the SQL community to deliver changes customers want. Centered on improving performance and diagnostics, SQL Server 2016 Service Pack 2 (SP2) comes with several changes.

In terms of performance and scalability, Microsoft cleaned up the DB cleanup process. The company found large distribution databases were causing blocks. Service Pack 2 improves the process to ensure many blockage situations are resolved.

SQL Server 2016 also now supports cancelling requests through setting CPU time in Resource Governor. Furthermore, the platform also gets improved Indirect Checkpoint for TempDB and improved database backup performance.

Below is the full changelog for 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. More extensive performance testing results on various database sizes is shared below. 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

Microsoft has also detailed the changes made to diagnostics in SQL Server 2016. This Service Pack introduces Full DTC support for databases in Availability Group. New DBCC CLONEDATABASE options have also been added to help users verify clone and backups.

Here is the changelog:

  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 DMV to monitor VLF information – A new DMV sys.dm_db_log_info is introduced in SQL Server 2016 SP2 to expose the VLF information similar to DBCC LOGINFO to monitor, alert and avert potential T-Log issues experienced by customers.
  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.