SQL Server 2012 Service Pack 4 (SP4) Released!

SQL Server Product Team is excited to bring you the final release of SQL Server 2012 Service Packs, Service Pack 4 (SP4). This release of SQL 2012 Service Pack has 20+ improvements centered around performance, scalability and diagnostics based on feedback from customers and the SQL Server community. These improvements enable SQL Server 2012 to perform faster and scale out of the box on modern hardware design.

In addition to improvements listed below, SQL Server 2012 SP4 includes all the fixes up to and including SQL Server 2012 SP3 CU10.

Following is the detailed list of improvements introduced in SQL Server 2012 SP4.

Performance and Scalability Improvements

• 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 if it becomes a bottleneck. Unpartitioned memory objects can be dynamically promoted to be partitioned by node (number of partitions equals number of NUMA nodes), and memory objects partitioned by node can by further promoted to be partitioned by CPU (number of partitions equals number of CPUs).
• 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.
• Automatic Soft NUMA partitioning – When Trace Flag 8079 is enabled during startup, SQL Server 2012 SP4 will interrogate the hardware layout and automatically configure 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.
• Improved Distribution agent cleanup procedure - An oversized distribution database tables caused blocking and deadlock situation. An improved cleanup procedure aims to eliminate some of these blocking or deadlock scenarios.
• Change Tracking Cleanup – A new stored procedure sp_flush_CT_internal_table_on_demand is introduced to cleanup change tracking internal tables on demand.

Supportability and Diagnostics Improvements

• Full Dumps support for Replication Agents - Today if replication agents encounter an unhandled exception, the default is to create a mini dump of the exception symptoms. This makes troubleshooting unhandled exception issues very difficult. Through this change we are introducing a new Registry key, which would allow to create a full dump for Replication Agents. You need to add a new DWORD key – ReplAgentFullDump with data as 1 in HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\XXX registry hive (XXX refers to SQLGLOBAL_VERSION for example, 110 for SQL 2012). After this change, when the replication agent encounters an unhandled exception it will generate a full dump instead of minidump.
• Extended diagnostics in showplan XML - Showplan XML has been extended to expose information about enabled trace flags, memory fractions for optimized nested loop join, CPU time and elapsed time.
• Better correlation between diagnostics Extended Event and DMVs - This improvement addresses connect feedback (1934583). Query_hash and query_plan_hash fields are used for identifying a query uniquely. DMV defines them as varbinary(8), while Extended Event defines them as UINT64. Since SQL server does not have “unsigned bigint”, casting does not always work. This improvement introduces new Extended Event action/filter columns equivalent to query_hash and query_plan_hash except they are defined as INT64 which can help correlating queries between Extended Event and DMVs.
• Better memory grant/usage diagnostics - New query_memory_grant_usage Extended Event.
• Add protocol tracing to SSL negotiation steps - Add bid trace information for successful/failed negotiation, including the protocol etc. This can be useful when troubleshooting connectivity scenarios while, for example, deploying TLS 1.2.
• Setting correct compatibility level for distribution database - After Service Pack Installation the Distribution database compatibility level changes to 90. This was because of a code path in sp_vupgrade_replication stored procedure. The SP has now been changed to set the correct compatibility level for the distribution database.
• 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.
• 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.
• Extended Events enhancement for read routing failure for an Availability Group - Currently the read_only_route_fail Extended Event only gets fired if there is a routing list present, but none of the servers in the routing list are available for connections. In this improvement, we are including additional information to assist with troubleshooting and also expand on the code points where this Extended Event gets fired.
• Proper handling of Service Broker with Availability group failover - In the current implementation when Service Broker is enabled on an Availability Group Databases, during an AG failover all Service broker connections which originated on the Primary Replica are left open. This improvement targets to close all such open connections during an AG failover.
• Programmatically identify LPIM to SQL service account – New sql_memory_model, sql_memory_model_desc columns in DMV sys.dm_os_sys_info to allow DBAs to programmatically identify if Lock Pages in Memory (LPIM) privilege is in effect at the service startup time.
• Programatically identify IFI privilege to SQL service account – New column instant_file_initialization_enabled in DMV sys.dm_server_services to allow DBAs to programmatically identify if Instant File initialization (IFI) is in effect at the SQL Server service startup.

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.

We are releasing this service pack after mainstream support ended in July, 2017. To learn more about Microsoft Support Lifecycle Policy, please read here. Please refer to the FAQ below for further details on SP3 and SP4 support.

The SQL Server 2012 SP4 installation may require reboot post installation. As a best practice, we recommend to plan and perform a reboot following the installation of SQL Server 2012 SP4.

The Service Pack is available for download on the Microsoft Download Center and will also be made available on Azure Images Gallery, MSDN, MBS/Partner Source, and VLSC in the coming weeks. As part of our continued commitment to software excellence for our customers, this upgrade is available to all customers with existing SQL Server 2012 deployments.
To obtain SQL Server 2012 SP4, please visit the links below:
SQL Server 2012 SP4 SQL Server 2012 SP4 Feature Pack SQL Server 2012 Service Pack 4 Release Information

Thank you,

Microsoft SQL Server Engineering Team