SQL Server 2014 Service Pack 3 is now Available!!!

  • Article

The SQL Server team is excited to bring you SQL Server 2014 Service Pack 3 (SP3). SQL Server 2014 SP3 contains 25+ improvements centered around performance, scalability and diagnostics based on the feedback from customers and SQL community. These improvements enable SQL Server 2014 to perform faster and scale out of the box on modern hardware design. It also showcases the SQL Product Team’s commitment to provide continued value into in-market releases. Additional Release information found in KB4022619.

Following is the detailed list of improvements introduced in SQL 2014 SP3.

Improvements in SQL 2014 SP3

  • 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.
  • Change Tracking Cleanup - Improved change tracking cleanup performance and efficiency for Change Tracking side tables.
  • Support for cancelling request through setting CPU time out in Resource Governor – Improves the handling of query requests by cancelling the request if CPU thresholds for a request is reached.
  • Select INTO to create target table in the desired filegroup - Starting SQL Server 2014 SP3, 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 TSQL syntax.
  • Improved database backup performance on large memory machines - SQL Server 2014 SP3 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 IO 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.
  • Improved database restore performance for compressed backups: SQL Server 2014 SP3 improves restore performance on 4K-sector volumes when backups are compressed
  • 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)
  • 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 Improvements

  • Update to Sys.databases is_encrypted column to accurately reflect encryption status for TempDB - The value of is_encrypted 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 SQL Server 2014 SP3, sys.databases.is_encrypted now accurately reflects encryption status for TempDB.
  • NEW DBCC CLONEDATABASE Options to generate verified clone and backup - With SQL Server 2014 SP3, DBCC CLONEDATABASE allows two new options to produce a) verified clone b) backup clones. 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 CSS for troubleshooting.
  • New DMV to monitor TempDB version store space usage - This new DMV will be useful in monitoring TempDB for version store usage for dbas who can proactively plan TempDB sizing based on the version store usage requirement per database without any performance toll or overheads of running it on production servers.
  • 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.
  • Service Broker support for DBCC CLONEDATABASE – Enhanced DBCC CLONEDATABASE command to allow scripting of SSB objects.
  • XEvents enhancement for read routing failure for an Availability Group - Currently the read_only_route_fail XEvent only gets fired if there is a routing list present, but none of the servers in the routing list are available for connections. In this improvement we are including additional information to assist with troubleshooting and also expand on the code points where this XEvent gets fired.
  • New DMVs to monitor transaction log information – Two new transaction log DMVs are introduced in SQL Server 2014 SP3: 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.
    • dm_db_log_stats returns summary level information about the log.
    • dm_db_log_info exposes the VLF information similar to DBCC LOGINFO.
  • Processor Information in Sys.dm_os_sys_info DMV – 3 new columns added to the sys.dm_os_sys_info DMV to expose the processor related information like socket_count, cores_per_numa, etc.
  • Extent modified information in sys.dm_db_file_space_usage –  A new column has been added to sys.dm_db_file_space_usage to track the number of modified extents since the last full backup.
  • Setting correct compatibility level for distribution database - After Service Pack Installation, the Distribution database compatibility level changes to 90. This was because of a code path in sp_vupgrade_replication stored procedure. The SP has now been changed to set the correct compatibility level for the distribution database.
  • 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.
  • SQL Server 2014 SP3 emits database name in deadlock graph
  • Showplan XML can include new attribute EstimateRowsWithoutRowgoal if Query Optimizer uses “row goal” logic.
  • Actual showplan XML extended to add UdfCpuTime and UdfElapsedTime to track time spent in scalar User-Defined Functions.
  • Replication Support for databases with Supplemental characters collations – Replication is now supportable on databases which use the Supplemental Character Collation.
  • 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.
  • 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.
  • Better memory grant/usage diagnostics - New query_memory_grant_usage XEvent
  • Extended diagnostics in showplan XML - Showplan XML has been extended to expose information about memory fractions for optimized nested loop join, CPU time and elapsed time.
  • Added a new column to DMV sys.dm_sql_referenced_entities to allow try-catch scenarios backwards compatible with SQL Server 2008

SQL Server 2014 SP3 contains a roll-up of solutions provided in SQL Server 2014 cumulative updates up to and including the SQL Server 2014 SP2 Cumulative Update 13 (CU13).

The Service Pack is available for download on the Microsoft Download Center, and is also available on the Microsoft Update Catalog, MSDN, Eval Center, MBS/Partner Source and VLSC. As part of our continued commitment to software excellence for our customers, this upgrade is available to all customers with existing SQL Server 2014 deployments.

To obtain SQL Server 2014 SP3, please visit the links below:

Thank you,
Microsoft SQL Server Engineering Team