Microsoft is pleased to announce the release of SQL Server 2012 Service Pack 3. This service pack provides updates to help workloads from a performance, scalability and diagnostics perspective. The key updates are listed below.
Improvements for performance and scalability
- Trace flags 1236 and 9024 were introduced in SQL Server 2012 to address lock contention and log write waits for systems with high number of transactions. SQL Server 2012 SP3 makes this behavior default without having to use the trace flags. This ensures that your system is able to scale up when required without any user action required. The default behavior was already introduced in SQL Server 2014 Service Pack 1.
- Improvements to consistency check performance by estimating memory grants correctly and making optimum use of CPU and Memory (KB3029825).
- Improvements while performing a SELECT INTO operation involving a temporary table. This is a common scenario when database operations involve temporary or staging tables.
- 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.
- Improvements to enhance the performance of opening and reading the (.xel) Extended Event files (KB3112710). This allows you to analyze data faster while troubleshooting SQL Server related issues.
- If you use spatial data in your application, then SP3 has improvements for spatial query performance (KB3107399).
Improved diagnostic and troubleshooting capabilities
- Added new logging capability for Lease Timeout messages so that the current time and the expected renewal times are logged.
- There is a new error message for lease workers that clearly indicates the reason for the Lease Timeout. This would allow you to troubleshoot failover issues more effectively.
- Lease stages for Availability Groups are now indicated through new extended events for lease workers.
- Improvements in non-yield detection logic to prevent false positives for scheduler non-yield messages.
- Improved diagnostic messages while performing backups using third-party applications that make use of SQLVDI.DLL.
Improvements for query performance troubleshooting
- Sys.dm_exec_query_stats DMV will now report (KB3107398) information on memory grants, degree of parallelism and the threads used for executing the query. This information is normally available in the XML plan which can be now retrieved using the DMV as well.
- Spills originating from SORT operations are reported correctly while tracking execution statistics. Now, SET STATISTICS IO option now exposes information about SORT operations.
- 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.
- 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).
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, SP3 contains a roll-up of solutions provided in SQL Server 2012 cumulative updates up to and including the latest SP2 Cumulative Update – CU9. Therefore, there is no requirement to wait for SP3 CU1 to ‘catch-up’
with 2012 SP2 CU content.
The Service Pack is available for download on the Microsoft Download Center, and will be made available via Microsoft Update on 8th December, 2015. It will also be available on MSDN, MAPS/MPN, 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 SP3, please visit the links below:
Microsoft SQL Server Engineering Team