March 2015 - Glenn Berry

As of May 16, 2016, there have been twelve Cumulative Updates (CU) for the Service Pack 2 branch of SQL Server 2012. There have been a fairly high number of hotfixes in every one of these Cumulative Updates, as more people are using SQL Server 2012. If you are running SQL Server 2012 Service Pack 2, I really think you should be running the latest SQL Server 2012 Cumulative Update. Right now, that means Service Pack 2, CU12 (Build 11.0.5649), which was released on May 16, 2016. Ideally, you should be moving to SQL Server 2012 Service Pack 3, which was released on November 22, 2015.

If you are still on the SQL Server 2012 SP1 branch, then you want to be on SP1 CU16. But really, you should be on the SP2 branch as soon as possible, since the SP1 branch is no longer a supported branch. Actually, it you are still on the SP1 branch, you should jump to the SP3 branch, and skip SP2.

Table 1 shows the SQL Server 2012 SP2 CU builds that have been released so far.

Build	Description	Release Date
11.0.5058	SP2 RTM	June 10, 2014
11.0.5532	SP2 CU1	July 23, 2014
11.0.5548	SP2 CU2	September 15, 2014
11.0.5556	SP2 CU3	November 17, 2014
11.0.5569	SP2 CU4	January 19, 2015
11.0.5582	SP2 CU5	March 16, 2015
11.0.5592	SP2 CU6	May 18, 2015
11.0.5623	SP2 CU7	July 20, 2015
11.0.5634	SP2 CU8	September 21, 2015
11.0.5641	SP2 CU9	November 16, 2015
11.0.5644	SP2 CU10	January 18, 2016
11.0.5646	SP2 CU11	March 21, 2016
11.0.5649	SP2 CU12	May 16, 2016

Table 1: SQL Server 2012 SP2 CU Builds

You can follow the KB article link below to see all of the CU builds for the SQL Server 2012 Service Pack 2 branch.

SQL Server 2012 SP2 Build Versions

Like I did for the SQL Server 2012 SP1 branch, I decided to scan the hotfix list for all of the Cumulative Updates in the SP2 branch, looking for performance and general reliability-related fixes for the SQL Server Database Engine. I came up with the list below, but this listing is completely arbitrary on my part. You may come up with a completely different list, based on what specific SQL Server 2012 features you are using.

Here are the fixes in the Service Pack 2 branch:

SQL Server 2012 SP2 Cumulative Update 1 (Build 11.0.5532), 43 total public hot fixes

FIX: Intense query compilation workload does not scale with growing number of cores on NUMA hardware and results in CPU saturation in SQL Server

FIX: Assertion failure when you execute a query specifying TOP N and ORDER BY in SQL Server

FIX: Poor cardinality estimation when the ascending key column is branded as stationary in SQL Server

FIX: Poor performance on I/O when you execute select into temporary table operation in SQL Server 2012

FIX: Parallel deadlock or self-deadlock occurs when you run a query that results in parallelism in SQL Server 2012

FIX: Data corruption occurs in clustered index when you run online index rebuild in SQL Server 2012 or SQL Server 2014

SQL Server 2012 SP2 Cumulative Update 2 (Build 11.0.5548), 43 total public hot fixes

FIX: SQL Cache Memory decreases and CPU usage increases when you rebuild an index for a table in SQL Server

FIX: Incorrect result when you execute a query that uses WITH RECOMPILE option in SQL Server 2012

FIX: Memory leak occurs when you start and stop an XEvent session repeatedly in SQL Server 2012

FIX: Cannot reclaim unused space by using shrink operation in the table that contains a LOB column in SQL Server

FIX: Undetected deadlock occurs when you use a sequence object in SQL Server 2012

FIX: Performance improvement for SQL Server Spatial data access in SQL Server 2012

FIX: A severe error occurs when you run a query that uses CTEs against the partitioned tables in SQL Server 2012

FIX: UPDATE STATISTICS performs incorrect sampling and processing for a table with columnstore index in SQL Server 2012

SQL Server 2012 SP2 Cumulative Update 3 (Build 11.0.5556), 32 total public hot fixes

FIX: Poor performance for cdc.fn_cdc_get_net_changes_<capture_instance> in SQL Server 2012 SP2

FIX: “Non-yielding Scheduler” condition occurs when you run a complex query in SQL Server 2012

FIX: INSERT performance decreases in merge replication that uses precomputed partitions in SQL Server 2012

FIX: Error when you execute statistics update on a table that has sql_variant data type in SQL Server 2012

FIX: Large chain of blocking occurs when you use merge replication in SQL Server 2012

SQL Server 2012 SP2 Cumulative Update 4 (Build 11.0.5569), 36 total public hot fixes

FIX: High CPU consumption when you use spatial data type and associated methods in SQL Server 2012 or SQL Server 2014

FIX: Sequence object generates duplicate sequence values when SQL Server 2012 is under memory pressure

FIX: SQL Server is in script upgrade mode for a long time after you apply a SQL Server hotfix or security update

FIX: DBCC CHECKDB and DBCC CHECKTABLE take longer to run when SQL CLR UDTs are involved in SQL Server 2012

SQL Server 2012 SP2 Cumulative Update 5 (Build 11.0.5582), 27 total public hot fixes

FIX: SOS_CACHESTORE spinlock contention on system table rowset cache causes high CPU usage in SQL Server 2012 or 2014

FIX: SOS_CACHESTORE spinlock contention on ad hoc SQL Server plan cache causes high CPU usage in SQL Server 2012 or 2014

FIX: Memory leak in USERSTORE_SCHEMAMGR and CPU spikes occur when you use temp table in SQL Server 2012

FIX: AlwaysOn availability groups are reported as NOT SYNCHRONIZING

FIX: Complex parallel query does not respond in SQL Server 2012

FIX: Performance issue occurs when you use sqlncli11.dll ODBC driver to access SQL Server in an application

FIX: SQL Server 2012 shuts down unexpectedly when you run DBCC CHECKDB and the rollback recovery on the snapshot fails

SQL Server 2012 SP2 Cumulative Update 6 (Build 11.0.5592), 23 total public hot fixes

FIX: Corruption occurs on the page of secondary replica when you change the secondary replica to unreadable

FIX: SQL Server takes long time to open database after recovery to add or remove a data file in SQL Server 2014 or SQL Server 2012

FIX: Access violation when a missing task for a background clear operation tries to delete a missing index entry

SQL Server 2012 SP2 Cumulative Update 7 (Build 11.0.5623), 32 total public hot fixes

FIX: The change table is ordered incorrectly for updated rows after you enable change data capture for a Microsoft SQL Server 2012 database

FIX: Hash or merge join hints may be ignored when you execute a query in SQL Server 2012

FIX: “Unable to create restore plan due to break in the LSN chain” error when you restore differential backup in SSMS

FIX: Indexed view returns incorrect result after insert or delete operation on the base table in SQL Server 2012

FIX: Schema lock occurs on a table with large data when you delete lots of rows from it in SQL Server 2012

FIX: Error messages are logged when you execute a non-cacheable auto-parameterized query in SQL Server 2012

FIX: Incorrect results occur in a rare scenario when you run a query that contains a nested loop join and performance spool in its inner side in SQL Server 2012

FIX: A deadlock occurs when you execute the sp_estimate_data_compression_savings stored procedure in SQL Server 2012

FIX: Out of memory error when the virtual address space of the SQL Server process is very low on available memory

FIX: The Resource database is missing after you install updates or service packs for instances of SQL Server 2012 one after another and then restart the server

FIX: Incorrect result when you execute a full-text query with parallel execution plan in SQL Server 2012

FIX: Contention occurs when you execute many ad-hoc queries in SQL Server 2012

FIX: You receive incorrect results from the CHANGETABLE() function when snapshot isolation for a SQL Server 2012 database

FIX: Cannot enable “change data capture” on a table that uses a unique index with more than one included column in SQL Server 2012 SP2

SQL Server 2012 SP2 Cumulative Update 8 (Build 11.0.5634), 23 total public hot fixes

FIX: A stalled dispatcher system dump forces a failover and service outage in SQL Server 2014 or SQL Server 2012

FIX: Error 602 when you run a stored procedure repeatedly in SQL Server 2012

FIX: Data spill occurs when you sort large amount of data in SQL Server 2012

FIX: FileStream Share Access fails when you use AlwaysOn Availability Groups feature in SQL Server 2012

FIX: Rare incorrect result occurs when you run parallel query in SQL Server 2012

FIX: It takes a longer time than earlier versions of SQL Server when you run DBCC CHECKDB in SQL Server 2012

FIX: Access violation occurs in AlwaysOn availability groups scenarios in SQL Server 2012

FIX: Access violations when you use the FileTable feature in SQL Server 2012

FIX: Out of memory error when the virtual address space of the SQL Server process is very low on available memory

FIX: SQLDiag fails with function 87 error in SQL AlwaysOn scenario where the server name has 15 characters

FIX: Scheduler deadlock on primary replica when you remove a replica from an AlwaysOn availability group in SQL Server 2012

FIX: Error 9002 and error 3052 when you try to add or back up log file in SQL Server 2012

SQL Server 2012 SP2 Cumulative Update 9 (Build 11.0.5641), 13 total public hot fixes

FIX: The SQL Server service performs more I/O operations on the mirror server than on the principal server

FIX: Backup operation fails in a SQL Server database after you enable change tracking

FIX: Performance decrease when an application that has connection pooling frequently connects or disconnects in SQL Server

SQL Server 2012 SP2 Cumulative Update 10 (Build 11.0.5644), 12 total public hot fixes

FIX: You cannot use the Transport Layer Security protocol version 1.2 to connect to a server that is running SQL Server 2014 or SQL Server 2012

FIX: You receive errors when you execute a query that uses tempdb in SQL Server

FIX: The Log Reader Agent stops intermittently and an Access Violation occurs in SQL Server 2012

SQL Server 2012 SP2 Cumulative Update 11 (Build 11.0.5646), 15 total public hot fixes

FIX: Numeric overflow when you run a query that spills more than 65,535 extents to tempdb in SQL Server 2014 or 2012

FIX: “A severe error occurred on the current command” error occurs when a Table-Valued User-Defined function is referred to by using a synonym

FIX: An assertion failure occurs on the secondary replica when you use the AlwaysOn Availability Groups feature in SQL Server 2012

FIX: SQL Server stops responding when you back up the certificate that is used to encrypt the database encryption key in SQL Server 2012 or SQL Server 2014

FIX: “Cannot resolve the collation conflict” error occurs when you apply a snapshot to the subscriber database in SQL Server

FIX: Slow performance occurs when you query numeric data types from an Oracle database

FIX: An access violation occurs when you execute a stored procedure that uses a cursor on a table variable in SQL Server

FIX: XA transactions are not cleaned when you exit a Java application in an instance of SQL Server

SQL Server 2012 SP2 Cumulative Update 12 (Build 11.0.5649), 8 total public hot fixes

FIX: Memory leak occurs on the AlwaysOn secondary replica when change tracking is enabled in SQL Server 2012

FIX: Error when you use the replication feature in SQL Server 2014 or SQL Server 2012

FIX: “Non-yielding Scheduler” error when versioning cleanup task runs on a SQL Server AlwaysOn secondary replica

Once again, the idea here is to give you a lot of concrete reasons to want to stay current with the latest SQL Server 2012 SP and CU, by pointing out some of the more valuable fixes in each CU in the Service Pack 2 branch. If my opinion does not sway everyone, this relatively new Microsoft KB article might be more convincing:

Announcing updates to the SQL Server Incremental Servicing Model (ISM)

Another very useful resource is this Microsoft KB article:

Recommended updates and configuration options for SQL Server 2012 and SQL Server 2014 used with high-performance workloads

Happily, Microsoft has been updating this KB article with new information, so you might want to read it again, if you have not done so already.

I revised several of the queries this month in all five versions of the script. Once again, the main enhancements are additional comments and documentation about the queries, including more information about how to interpret the results.

Rather than having a separate blog post for each version, I have just put the links for all five major versions in this single post. There are two separate links for each version. The first one on the top left is the actual query script, and the one below on the right is the matching blank results spreadsheet.

SQL Server 2005 Diagnostic Information Queries

SQL Server 2005 Blank Results

SQL Server 2008 Diagnostic Information Queries

SQL Server 2008 Blank Results

SQL Server 2008 R2 Diagnostic Information Queries

SQL Server 2008 R2 Blank Results

SQL Server 2012 Diagnostic Information Queries

SQL Server 2012 Blank Results

SQL Server 2014 Diagnostic Information Queries

SQL Server 2014 Blank Results

The basic idea is that you should run each query in the set, one at a time (after reading the directions for that query). It is not really a good idea to simply run the entire batch in one shot, especially the first time you run these queries on a particular server, since some of these queries can take some time to run, depending on your workload and hardware. I also think it is very helpful to run each query, look at the results and think about the emerging picture of what is happening on your server as you go through the complete set.

You need to click on the top left square of the results grid in SSMS to select all of the results, and then right-click and select “Copy with Headers” to copy all of the results, including the column headers to the Windows clipboard. Then you paste the results into the matching tab in the blank results spreadsheet. There are also some comments on how to interpret the results after each query.

About half of the queries are instance specific and about half are database specific, so you will want to make sure you are connected to a database that you are concerned about instead of the master system database. Running the database-specific queries while being connected to the master database is a very common mistake that I see people making when they run these queries.

Note: These queries are stored on Dropbox. I occasionally get reports that the links to the queries and blank results spreadsheets do not work, which is most likely because Dropbox is blocked wherever people are trying to connect.

I also occasionally get reports that some of the queries simply don’t work. This usually turns out to be an issue where people have some of their user databases in 80 compatibility mode, which breaks many DMV queries.

It is also very important that you are running the correct version of the script that matches the major version of SQL Server that you are running. There is an initial query in each script that tries to confirm that you are using the correct version of the script for your version of SQL Server. If you are not using the correct version of these queries for your version of SQL Server, some of the queries are not going to work correctly.

If you want to understand how to better run and interpret these queries, you should consider listening to my three latest Pluralsight courses, which are SQL Server 2014 DMV Diagnostic Queries – Part 1 , SQL Server 2014 DMV Diagnostic Queries – Part 2 and SQL Server 2014 DMV Diagnostic Queries – Part 3. All three of these courses are pretty short and to the point, at 67, 77, and 68 minutes respectively. Listening to these three courses is best way to thank me for maintaining and improving theses scripts…

Please let me know what you think of these queries, and whether you have any suggestions for improvements. Thanks!

Semi-random musings about SQL Server performance

Performance and Stability Related Fixes in Post-SQL Server 2012 SP2 Builds

SQL Server Diagnostic Information Queries for March 2015

SQL Server 2012 SP2 CU5 Available