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

07/12/2016

7 minutes to read

In this article

The SQL Server team is excited to bring you SQL Server 2014 Service Pack 2 (SP2). SQL Server 2014 SP2, in addition to a rollup of released hotfixes including SQL 2014 SP1 CU7 contains 20+ 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.

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

Performance and Scalability Improvements in SQL 2014 SP2

Automatic Soft NUMA partitioning – With SQL 2014 SP2, Automatic Soft NUMA is introduced when Trace Flag 8079 is enabled at the server level . When Trace Flag 8079 is enabled during startup, SQL Server 2014 SP2 will interrogate the hardware layout and automatically configures Soft NUMA on systems reporting 8 or more CPUs per NUMA node .The automatic soft NUMA behavior is Hyperthread (HT/logical processor) aware . The partitioning and creation of additional nodes scales background processing by increasing the number of listeners, scaling and network and encryption capabilities. It is recommended to first test the performance of workload with Auto-Soft NUMA before it is turned ON in production.

. When Trace Flag 8079 is enabled during startup, SQL Server 2014 SP2 will interrogate the hardware layout and automatically configures Soft NUMA on systems reporting .The automatic soft NUMA behavior is . The partitioning and creation of additional nodes scales background processing by increasing the number of listeners, scaling and network and encryption capabilities. It is recommended to first test the performance of workload with Auto-Soft NUMA before it is turned ON in production. Dynamic Memory Object Scaling - Dynamically partition memory object based on number of nodes and cores to scale on modern hardware. The goal of dynamic promotion is to automatically partition a thread safe memory object (CMEMTHREAD) if it becomes a bottleneck. Unpartitioned memory objects will be dynamically promoted to be partitioned by node (number of partitions equals number of NUMA nodes) based on the workload and bottleneck, and memory objects partitioned by node can be further promoted to be partitioned by CPU (number of partitions equals number of CPUs). This enhancement eliminates the need of Trace Flag 8048 post SQL 2014 SP2.

a thread safe memory object (CMEMTHREAD) if it becomes a bottleneck. Unpartitioned memory objects will be to be partitioned by node (number of partitions equals number of NUMA nodes) based on the workload and bottleneck, and memory objects partitioned by node can be further promoted to be partitioned by CPU (number of partitions equals number of CPUs). This enhancement the need of Trace Flag 8048 post SQL 2014 SP2. MAXDOP hint for DBCC CHECK* commands - This improvement addresses one of the connect feedback from customers and is useful to run DBCC CHECKDB with MAXDOP setting other than the sp_configure value. If MAXDOP exceeds the value configured with Resource Governor, the Database Engine uses the Resource Governor MAXDOP value, described in ALTER WORKLOAD GROUP (Transact-SQL). All semantic rules used with the max degree of parallelism configuration option are applicable when you use the MAXDOP query hint.

setting other than the sp_configure value. If MAXDOP exceeds the value configured with Resource Governor, the Database Engine uses the Resource Governor MAXDOP value, described in ALTER WORKLOAD GROUP (Transact-SQL). All semantic rules used with the max degree of parallelism configuration option are applicable when you use the MAXDOP query hint. Enable >8TB for Buffer Pool - Enabled 128TB Virtual address space for buffer pool usage. This improvement enables SQL Server Buffer Pool to scale beyond 8TB on modern hardware.

for buffer pool usage. This improvement enables SQL Server Buffer Pool to scale on modern hardware. SOS_RWLock spinlock Improvement - The SOS_RWLock is a synchronization primitive used in various places throughout the SQL Server code base. As the name implies the code can have multiple shared (readers) or single (writer) ownership. This improvement removes the need for spinlock for SOS_RWLock and instead uses lock-free techniques similar to in-memory OLTP. With this change, many threads can read a data structure protected by SOS_RWLock in parallel without blocking each other and thereby providing increased scalability. Before this change, the older spinlock implementation allowed only one thread to acquire the SOS_RWLock at a time even to read a data structure.

Spatial Native Implementation – Significant improvement in spatial query performance which was introduced earlier in SQL 2012 SP3 is now introduced in SQL 2014 SP2 as well through native implementation (KB3107399)

Supportability and Diagnostics Improvements

We will be updating and adding follow-up posts on the Tiger blog in the coming weeks to describe some of the above improvements in detail.

As noted above, SP2 contains a roll-up of solutions provided in SQL Server 2014 cumulative updates up to and including the latest SP1 Cumulative Update – CU7 . Therefore, there is no requirement to wait for SP2 CU1 to ‘catch-up’ with 2014 SP1 CU content.

The Service Pack is available for download on the Microsoft Download Center, and is also available on the Microsoft Download Center, 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 SP2, please visit the links below:

SQL Server 2014 SP2 SQL Server 2012 SP2 Express SQL Server 2014 SP2 Feature Pack SQL Server 2014 SP2 Report Builder SQL Server 2014 SP2 Reporting Services Add-in for Microsoft Sharepoint SQL Server 2014 SP2 Semantic Language Statistics SQL Server 2014 Service Pack 2 Release Information

Thank you,

Microsoft SQL Server Engineering Team