SQLServerUpdates.com
  • Home – Most Recent Updates
    • SQL Server 2019 Updates
    • SQL Server 2017 Updates
    • SQL Server 2016 Updates
    • SQL Server 2014 Updates
    • SQL Server 2012 Updates
    • SQL Server 2008 R2 Updates
    • SQL Server 2008 Updates
  • Download SQL Server
  • Subscribe to Updates
  • Contact Us
    • Frequently Asked Questions

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

5 years ago
Brent Ozar
SQL Server 2016, Updates
5 Comments

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

Danger

Check out the full list, and download SP2 here.

Brent Ozarhttp://sqlserverupdates.com
I make Microsoft SQL Server faster and more reliable. I love teaching, travel, and laughing.
Previous Post
Announcing SQL Server 2017 Cumulative Update 6
Next Post
Announcing SQL Server 2017 Cumulative Update 7

5 Comments. Leave new

  • David Williams
    April 25, 2018 9:37 am

    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

    Reply
    • David Williams
      April 25, 2018 9:43 am

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

      Reply
  • Pete
    May 8, 2018 9:26 pm

    Just applied SP2 and it comes with a lovely bug which stops SSAS from processing when called from SSIS.

    Reply
    • Francisco Tapia
      May 23, 2018 3:44 pm

      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

      Reply
  • Francisco Tapia
    May 23, 2018 3:42 pm

    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

    Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.

Subscribe

Want to get an email when Microsoft publishes a new SP or CU for SQL Server? Subscribe here.

Recent Updates

  • SQL Server 2022 Gets Its 2nd Update in 2 Days February 16, 2023
  • SQL Server 2022 Gets Its First Update! Plus 2019, 2017, 2016, 2014 Updates February 14, 2023
  • Announcing SQL Server 2019 CU15 January 27, 2022
  • Announcing SQL Server 2019 CU13 and SSMS 18.10: Replication Improvements October 5, 2021
  • Announcing 2016 Service Pack 3 and 2017 CU26 September 15, 2021

© 2021 Brent Ozar Unlimited®. All Rights Reserved. Privacy Policy