Announcing SQL Server 2016 Service Pack 2 (and this one is big!)

SP2 includes the hotfixes from all released 2016 cumulative updates: SQL Server 2016 CU1 through SP1 CU8. To get the itemized list of those, you’d need to click through the posts in our 2016 CU listing. Suffice it to say, there were a lot.

Here’s some of my favorite new performance tuning stuff:

• New columnstore fields in sys.dm_exec_query_stats
• New TempDB spill fields in sys.dm_exec_query_stats, procedure_stats, trigger_stats
• Optimizer row goals added to query plans
• User-defined-function stats added to query plans
• Better intra-query parallelism deadlocks troubleshooting tools
• Memory grants for nested loop joins weren’t shown in query plans
• Faster parallel queries when TOP is used with skewed data
• Plans now include a list of statistics loaded during optimization
• Add CXPACKET wait type to query plan
• CXPACKET/CXCONSUMER wait types inconsistent in wait stats DMVs
• Unified query plan schema across 2012 SP4 forward

Plus, if you scroll down to the bottom of the KB article, there are some intriguing “additional resolutions” that aren’t linked to more-info pages:

• Added new DMVs from 2017: sys.dm_db_log_stats, sys.dm_db_log_info, sys.dm_tran_version_store_space_usage
• Expose core count, sockets, NUMA info in sys.dm_server_services and SERVERPROPERTY
• Add filegroup support to SELECT INTO
• Fixed restore problems with compressed TDE backups (I’d heard from several folks struggling with this)
• Expose percentage of differential changes in the database (awesome for folks using Ola Hallengren’s backup scripts, which already supported these features in 2017)
• In-memory OLTP tables can now have a foreign key reference to non-durable memory-optimized tables
• Faster CHECKDB (that one’s a little vague as well)

Other updates:

• Fixed threadpool exhaustion for AGs using direct seeding
• Added MAXDOP parameter for creating & updating statistics
• Removing TDE from all databases now removes TDE on TempDB, too
• Fixed delay bug in incremental stats updates
• Fixed non-yielding scheduler bug caused by indirect checkpoints in TempDB
• Memory leak when capturing TVPs in XE
• Faster replication distribution agent cleanup
• Faster rollbacks (this KB article is wildly vague, so take this one with a grain of salt until they fix/rewrite it)

One warning, though: read the bottom note about “Uninstalling SQL Server 2016 SP2 (Not recommended):

Check out the full list, and download SP2 here.