Select Page

Service Pack 2 Available for Microsoft SQL Server 2014

Author: Eric Russo | | August 23, 2016

Early last month, Microsoft released Service Pack 2 (SP2) for Microsoft SQL Server 2014. This upgrade will be available to all users currently using SQL Server 2014. It can be downloaded here. The service pack can be applied to any of the editions of SQL Server 2014.

Highlights of the SQL Server 2014 SP2 include several improvements targeted towards performance upgrade, diagnostics and scalability, as well as rollup of the previously released hotfixes. Most of these enhancements are done based on the feedback from the SQL Server community. It also includes the improvements from SQL Server 2012 SP3, which was released after SQL Server 2014 SP1.

Key performance and scalability improvements

  • Automatic soft NUMA partitioning out-of-the-box when you enable the Trace Flag 8079 at the server level.
  • Ability to partition memory objects dynamically considering the number of cores, enabling scaling on the modern hardware.
  • Allows running the DBCC CHECKDB command with a MAXDOP setting other than the one defined in sp_configure. This is added in response to customer feedback.
  • A virtual address space of 128TB is allocated for the buffer pool usage.
  • 1236 and 9024 trace flags, introduced with SQL Server 2012 for database lock partitioning, is made the default behavior with SP2. This helps with scaling up the system, if required, without any user actions.
  • Performance improvements for spatial queries, which were introduced in SQL Server 2012 SP3, are available with SQL Server 2014 SP2.

Support and diagnostics improvements

  • A new DBCC command is added for cloning the database. This is helpful especially when troubleshooting production databases as it allows to clone schema and metadata.
  • BULK INSERT and BCP now support exporting and importing data encoded using UTF-8.
  • sp_flush_CT_internal_table_on_demand is a new stored procedure added to clean up the change tracking tables, on demand.
  • Query execution plans will include the statistics on actual rows read, which will help troubleshoot the query performance.
  • New logging capability has been added for lease timeout messages. It will log the current time, as well as the expected renewal time.
  • There’s a new error log message to indicate the no. of tempdb files, which includes the sizes and auto growth information about these files.

The detailed descriptions on the improvements made for the SP2 are available here.

With more than 600 database administrators worldwide, Datavail is the largest database services provider in North America. As a reliable provider of 24×7 managed services for applications, BI/Analytics, and databases, Datavail can support your organization, regardless of the build you’ve selected.

How to Solve the Oracle Error ORA-12154: TNS:could not resolve the connect identifier specified

The “ORA-12154: TNS Oracle error message is very common for database administrators. Learn how to diagnose & resolve this common issue here today.

Vijay Muthu | February 4, 2021

Data Types: The Importance of Choosing the Correct Data Type

Most DBAs have struggled with the pros and cons of choosing one data type over another. This blog post discusses different situations.

Craig Mullins | October 11, 2017

How to Recover a Table from an Oracle 12c RMAN Backup

Our database experts explain how to recover and restore a table from an Oracle 12c RMAN Backup with this step-by-step blog. Read more.

Megan Elphingstone | February 2, 2017

Subscribe to Our Blog

Never miss a post! Stay up to date with the latest database, application and analytics tips and news. Delivered in a handy bi-weekly update straight to your inbox. You can unsubscribe at any time.

Work with Us

Let’s have a conversation about what you need to succeed and how we can help get you there.


Work for Us

Where do you want to take your career? Explore exciting opportunities to join our team.