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):

5 Comments. Leave new
The Faster CHECKDB one should be “”Exposes the last known good DBCC CHECKDB date and time for a database.” https://twitter.com/mssqltiger/status/988817266618724352
I am following up on the Faster rollbacks one as well, so far – “The fix is that the rollback without restart becomes faster.” https://twitter.com/SQLPedro/status/988840639511937024
As per https://blogs.msdn.microsoft.com/sqlreleaseservices/sql-server-2016-service-pack-2-sp2-released/ the last known good DBCC CHECKDB time can be obtained from DATABASEPROPERTYEX([database], ‘lastgoodcheckdbtime’)
Just applied SP2 and it comes with a lovely bug which stops SSAS from processing when called from SSIS.
Many users are having the SSAS bug
https://www.sqlservercentral.com/Forums/Topic1935421.aspx
https://stackoverflow.com/questions/50266882/error-could-not-load-file-or-assembly-microsoft-analysisservices-adomdclientui
It looks like SP2 is causing AdomdClientUI errors connecting to SSAS servers. If you depend on connecting to a SSAS server, SP2 is effectively breaking connectivity.
more:
https://stackoverflow.com/questions/50266882/error-could-not-load-file-or-assembly-microsoft-analysisservices-adomdclientui
https://www.sqlservercentral.com/Forums/Topic1935421.aspx