Service Pack 2 Available for Microsoft SQL Server 2014

By | In Database Administration, SQL Server | August 23rd, 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.

Contact Us
Eric Russo
Senior Vice President of Database Services
Eric Russo is SVP of Database Services overseeing all of Datavail’s database practices including project and managed services for MS SQL, Oracle, Oracle EBS, MySQL, MongoDB, SharePoint and DB2. He is also the Product Owner for Datavail Delta, a database monitoring tool. He has 21 years’ experience in technology including 16 years in database management. His management success and style has attracted top DBAs from around the world to create one of the most talented and largest SQL Server teams. He has been with Datavail since 2008: previous to that his work experiences include DBA Manager at StrataVia, Senior Web Developer at Manifest Information Systems and SQL Server DBA at Clark County, Nevada.

Leave a Reply

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