Service Pack 2 Available for Microsoft SQL Server 2014

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.