Partilhar via

SQL Server 2012 Service Pack 4 (SP4) Released!

  • Artigo

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.

ID Question Answer
1 Will I be able to open a case in Microsoft Support Services to get assistance for Service Pack 4? Yes. For details please read about the Microsoft Support Lifecycle Policy here.
2 Will there be General Distribution Releases (GDRs)/security updates for Service Pack 4? GDR is not a release vehicle that is scheduled. Microsoft ships a GDR in the event of the discovery of an issue that Microsoft believes should be fixed through a GDR. If such discoveries were made for SQL Server 2012 Service Pack 4, then yes, GDRs would be released in the context of the Lifecycle Policy of SQL Server 2012.
3 Will Microsoft ship other hotfixes/bug fixes for Service Pack 4? Yes. As stated in Microsoft Support Lifecycle Policy, additional hotfixes might be available with an Extended Hotfix Support agreement.
4 Will there be Cumulative Updates for Service Pack 4? No. Microsoft is not planning to ship any Cumulative Updates for Service Pack 4.
5 Will I be able to open a case in Microsoft Support Services to get assistance for Service Pack 3? Yes. For details please read about the Microsoft Support Lifecycle Policy here.
6 Will there be General Distribution Releases (GDRs)/security updates for Service Pack 3? GDR is not a release vehicle that is scheduled. Microsoft ships a GDR in the event of the discovery of an issue that Microsoft believes should be fixed through a GDR. If such discoveries were made for SQL Server 2012 Service Pack 3, then yes, GDRs would be released up to 12 months after SP4 release (10/4/2018).
7 Will Microsoft ship other hotfixes/bug fixes for Service Pack 3? No. We recommend you contact Microsoft Support Services to get assistance and find a solution to your problem. You might be asked to upgrade to SP4 as part of troubleshooting. Please refer to Question 3 if you need a non-security hotfix for SP4 to address your issue.
8 What are the current lifecycle dates for SQL Server 2012? Please see Microsoft Lifecycle Policy - SQL Server 2012.

 

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

Comments

  • Anonymous
    October 13, 2017
    Every day, the "Published" date on this article changes to 1 day earlier.on 10/11/2017 it said "October 4, 2017"on 10/12/2017 it said "October 3, 2017"today, 10/13/2017, it says "October 2, 2017"This is terrible for anyone tryign to figure out when it was ACTUALLY published, which was 10/5/2017 according to this SP4 announcement:https://blogs.msdn.microsoft.com/sqlreleaseservices/announcing-sql-server-2012-service-pack-4/
    • Anonymous
      October 13, 2017
      I don't see the regression dates you are reporting. Release blog post date continues to be Oct 2, 2017. The SP was released on Oct 3, 2017, as seen in the download center link. We then updated the blog post you link here only on Oct 5, 2017, to make sure you have one more discoverability point to get to the SP.
  • Anonymous
    October 16, 2017
    Is this available on WSUS ? My download guys says its not out yet
    • Anonymous
      October 25, 2017
      We are targeting November for WSUS.
  • Anonymous
    October 20, 2017
    The lifecycle on Microsoft SQL Server 2012 Service Pack 4 (https://support.microsoft.com/en-us/lifecycle/search?alpha=sql%20server%202012%20service%20pack) says Mainstream Support End Date 7/11/2017. That date is in the past ??????
    • Anonymous
      October 20, 2017
      For every product, mainstream support is for 5 years from RTM and hence it ended on 7/11/2017. Since SP4 was released as a final rollup of SQL Server 2012 at the end of mainstream, the date is in the past.
      • Anonymous
        November 13, 2017
        It always worries me when a major update is released and then the team says "OK. That's final. No more updates." I'm seen updates like that introduce non-security bugs and the development team's response was: "Too bad. The product is in extended support now."
  • Anonymous
    October 23, 2017
    Hi,Maybe a stupid question, but will the update be available in the catalog in order to deploy is with WSUS ?
    • Anonymous
      October 25, 2017
      It will be available. We are targeting November at the moment.
      • Anonymous
        November 30, 2017
        Is this released in WSUS now?
        • Anonymous
          December 07, 2017
          It is live now for ENU, DEU, and JPN. We are working on the remaining languages. Thank you for your patience.
      • Anonymous
        December 06, 2017
        Hi SQL Server Engineering Team,any news of the release on WSUS ?regards,
        • Anonymous
          December 07, 2017
          It is live now for ENU, DEU, and JPN. We are working on the remaining languages. Thank you for your patience.