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.

1. Sql Server 2014 Sp2 Requirements

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.
• 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.
• 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.
• 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.
• 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

Download microsoft sql server 2014 enterprise edition x86/x64 Microsoft® SQL Server™ is a database management and analysis system for e-commerce, line-of-business, and data warehousing solutions. Jun 17, 2014 - Download SQL Server 2016 Management Studio (June 2016 release). Download SQL Server 2008 Express R2 SP2. You likely just want. SQL Server 2014 SP1 installation iso file question. Is there a slipstream SQL 2014 SP1 ISO file available for download or do I install SQL 2014 RTM then SQL 2014 SP1 and then SP1 CU6.Which RTM CU6 file should be used? Developer edition is same as enterprise in functionality but you can ONLY use developer edition for development.

• Database Cloning – Clone database is a new DBCC command added that allows Microsoft CSS to troubleshoot existing production databases by cloning the schema and metadata without the data. The clone is created with the command DBCC clonedatabase(‘source_database_name’, ‘clone_database_name’). Cloned databases should not be used in production environments. To see if a database has been generated from a clonedatabase you can use the following command, select DATABASEPROPERTYEX(‘clonedb’, ‘isClone’).The return value of 1 indicates the database is created from clonedatabase while 0 indicates it is not a clone.
• Tempdb supportability – A new errorlog message indicating the number of tempdb files and notifying different size/autogrowth of tempdb data files at server startup.
• Database Instant File Initialization Logging – A new errorlog message indicating that Database Instant File Initialization is enabled/disabled at server startup.
• Module names in callstack – The Xevent callstack now includes modules names + offset instead of absolute addresses.
• New DMF for incremental statistics – This improvement address connect feedback (797156) to enable tracking the incremental statistics at the partition level. A new DMF sys.dm_db_incremental_stats_properties is introduced to expose information per-partition for incremental stats.
• Index Usage DMV behavior updated – This improvement addresses connect feedback (739566) from customers where starting SQL 2014 SP2, rebuilding an index will *not* clear any existing row entry from sys.dm_db_index_usage_stats for that index. The behavior now is to what it was in SQL 2008.
• Better correlation between diagnostics XE and DMVs – This improvement addresses connect feedback (1934583). Query_hash and query_plan_hash are used for identifying a query uniquely. DMV defines them as varbinary(8), while XEvent defines them as UINT64. Since SQL server does not have “unsigned bigint”, casting does not always work. This improvement introduces new XEvent action/filter columns equivalent to query_hash and query_plan_hash except they are defined as INT64 which can help correlating queries between XE and DMVs.
• Support for UTF-8 in BULK INSERT and BCP – This improvement addresses connect feedback (370419) where support for export and import of data encoded in UTF-8 character set is now enabled in BULK INSERT and BCP.
• Lightweight per-operator query execution profiling – While troubleshooting query performance, although showplan provides lot of information on the query execution plan and cost of operator in the plan but it has limited information on actual runtime statistics like (CPU, I/O Reads, elapsed time per-thread). SQL 2014 SP2 introduces these additional runtime statistics per operator in the Showplan as well as an XEvent (query_thread_profile) to assist troubleshooting query performance.
• Change Tracking Cleanup – A new stored procedure sp_flush_CT_internal_table_on_demand is introduced to cleanup change tracking internal tables on demand.
• Always ON Lease Timeout Logging – Added new logging capability for Lease Timeout messages so that the current time and the expected renewal times are logged. Also a new message was introduced in the SQL Errorlog regarding the timeouts.
• New DMF for retrieving input buffer in SQL Server – A new DMF for retrieving the input buffer for a session/request (sys.dm_exec_input_buffer) is now available. This is functionally equivalent to DBCC INPUTBUFFER.
• Mitigation for underestimated and overestimated memory grant – Added new query hints for Resource Governor through MIN_GRANT_PERCENT and MAX_GRANT_PERCENT (KB3107401). This allows you to leverage these hints while running queries by capping their memory grants to prevent memory contention.
• Query execution diagnostics for tempdb spill – Hash Warning and Sort Warnings now have additional columns to track physical I/O statistics, memory used and rows affected. We also introduced a new hash_spill_details extended event. Now you can track more granular information for your hash and sort warnings (KB3107172). This improvement is also now exposed through the XML Query Plans in the form of a new attribute to the SpillToTempDbType complex type (KB3107400). Set statistics on now shows sort worktable statistics.
• AlwaysON XEvents and performance counters to troubleshoot latency – New AlwaysON XEvents and performance counters to improve diagnostics when troubleshooting latency issues with AlwaysON. For more details on how to leverage these XEvents and performance counters please refer to this video.
• DROP DDL Support for Replication – SQL Server 2014 SP2 allows a table that’s included as an article in transactional replication publication to be dropped from the database and the publication(s). A table can be dropped only if the allow_drop property is set to TRUE on all the publications that have the table(s) as an article. If the property is set to FALSEfor any of the publications that contain the article, then the DROP TABLE operation will fail and report that replicated articles can’t be dropped.(KB 3170123)
• Improved diagnostics for query execution plans that involve residual predicate pushdown – The actual rows read will now be reported in the query execution plans (KB3107397) to help improve query performance troubleshooting. This should negate the need to capture SET STATISTICS IO separately. This now allows you to see information related to a residual predicate pushdown in a query plan.

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:

Thank you,
Microsoft SQL Server Engineering Team

Microsoft has announced the availability of SQL Server 2016 Service Pack 2 (SP2) recently. Currently, SP2 is only available for download separately and it will be offered in Visual Studio subscriptions, MBS/Partner Source and VLSC soon.

Service Pack 2 includes all the patches since SQL Server 2016 SP1 plus performance improvements, diagnostic additions. SP1 was released back in 2016 so you will see a lot of improvement in this release.

Quick Summary

• 2 How to get SQL Server 2016 SP2?
• 3 FAQs

Changes made in SQL Server 2016 SP2

Generally speaking, Microsoft has worked a lot over server and database performance. Some of the enhancement include the following:

• Performance and scale improvements for SQL Server, such as improved database backup performance on large memory machines and added backup compression support, which helps performance of almost all databases.
• Support and diagnostics enhancements, such as improved troubleshooting and additional information about statistics used during query plan optimization.
• New improvements based on Connect feedback items filed by the SQL Server Community.
• Some improvements originally introduced in SQL Server 2014 SP2 and SQL Server 2012 SP4.

If you want to go through the complete change log, you may check out SQL Server SP2 release notes here.

How to get SQL Server 2016 SP2?

You can download the Service Pack from below mentioned links. Installation is not an issue. Just run the downloaded file SQLServer2016SP2-KB4052908-x64-ENU.exe (For English) on the system where SQL Server 2016 SP1 is installed. Follow the setup instructions and you’ll be good to go.

It’s always recommended to backup configuration and databases of the server before applying the new changes.

Download SQL Server 2016 Service Pack 2 Standard/Enterprise

Download SQL Server 2016 SP2 English [774 MB]

Download SQL Server 2016 SP2 French [783 MB]

Download SQL Server 2016 SP2 German [783 MB]

Download SQL Server 2016 SP2 Italian [783 MB]

Download SQL Server 2016 SP2 Japanese [782 MB]

Download SQL Server 2016 SP2 Korean [782 MB]

Download SQL Server 2016 SP2 Portuguese (Brazil) [783 MB]

Download SQL Server 2016 SP2 Russian [783 MB]

Download SQL Server 2016 SP2 Spanish [783 MB]

FAQs

Some frequently ask questions that I have been looking at in different forums. I thought I should compile them into an FAQ section for your ease.

Q: When was SQL Server 2016 Service Pack 2 released?

Answer: SQL Server 2016 SP2 was released on 1st May 2018.

Q: Is SQL Server 2016 a cumulative update?

Answer: Yes SP2 is a cumulative update but you may be required to install at least SP1 before installing SP2. There is no need to install any other update.

Q: What about SQL Server 2016 Service Pack 3?

Answer: There is no news about SQL Server 2016 SP3. It may be released after one year or so. From SQL Server 2017, Microsoft has discontinued the Service Pack model.

Q:What is the difference between Hotfix, Cumulative Update and Service Pack?

Answer:Hotfix is released to fix a specific issue. A single hotfix can be used to resolve multiple issues related to a single scenario.

A Cumulative Update (CU) is a pack of all the previous hotfixes since the last cumulative update. This means if you have not installed all the hotfixes, a cumulative update installation will install those hotfixes for you.

A Service pack is a tested cumulative update of all the hotfixes and cumulative updates since the last service pack. Normally, service pack also contains new features and enhancements in addition to security fixes.

Q: Where can I download the feature pack for SQL Server 2016 SP2 in my preferred language?

Answer:Download SQL Server 2016 SP2 feature pack

If there is any other question related to the service pack, please let us know through comments below.

Sql Server 2014 Sp2 Requirements

Must Read Articles: