SQL Server Diagnostic Information Queries for April 2015

I revised several of the queries this month in only the SQL Server 2012 and 2014 versions of the script.  As usual, the main enhancements are additional comments and documentation about the queries, including more information about how to interpret the results. 

Since SQL Server 2008 R2 and older are out of Mainstream support from Microsoft (and because fewer of my customers are using these old versions of SQL Server), I am not going to be updating the scripts for these older versions of SQL Server every single month going forward. 

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 diagnostic query script, and the one below on the right is the matching blank results spreadsheet, with labeled tabs. 

Here are links to the latest version for SQL Server 2014 and 2012: 

SQL Server 2014 Diagnostic Information Queries (April 2015)

SQL Server 2014 Blank Results

 

SQL Server 2012 Diagnostic Information Queries (April 2015)

SQL Server 2012 Blank Results

 

Here are links to the most recent versions of these scripts for SQL Server 2008 R2 and older:

SQL Server 2008 R2 Diagnostic Information Queries

SQL Server 2008 R2 Blank Results

 

SQL Server 2008 Diagnostic Information Queries

SQL Server 2008 Blank Results

 

SQL Server 2005 Diagnostic Information Queries

SQL Server 2005 Blank Results

 

The basic instructions for using these queries 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 my comments on how to interpret the results) and think about the emerging picture of what is happening on your server as you go through the complete set. I have some comments in the script on how to interpret the results after each query.

You need to click on the top left square of the results grid in SQL Server Management Studio (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.

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, or that someone is running an incorrect version of the script for their version of SQL Server.

It is 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 1SQL 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 really the best way to thank me for maintaining and improving these scripts…

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

SQL Server 2014 RTM Cumulative Update 7 Available

Microsoft has released SQL Server 2014 RTM Cumulative Update 7, which is Build 12.0.2495. This cumulative update has 41 public hot fixes, several of which look pretty significant. I have updated my recent blog post Performance and Stability Related Fixes in Post-SQL Server 2014 RTM Builds to include CU7.

It probably comes as no surprise that I think that you should be making plans to get on this build after you have gone through your planning and testing process. I still run into many systems that are running the original RTM build of SQL Server 2014, which I think is a bad situation.

Performance and Stability Related Fixes in Post-SQL Server 2014 RTM Builds

As of April 18, 2016, there have been thirteen Cumulative Updates (CU) for the RTM branch of SQL Server 2014. There have been a fairly high number of hotfixes in every one of these Cumulative Updates, as more people are using SQL Server 2014. If you are running SQL Server 2014 RTM, I really think you should be running the latest SQL Server 2014 RTM Cumulative Update. Right now, that means RTM, CU13 (Build 12.0.2568), which was released on April 18, 2016. 

Table 1 shows the SQL Server 2014 RTM CU builds that have been released so far.

BuildDescriptionRelease Date
12.0.2342RTM CU1April 21, 2014
12.0.2370RTM CU2June 27, 2014
12.0.2402RTM CU3August 18, 2014
12.0.2430RTM CU4October 21, 2014
12.0.2456RTM CU5December 17, 2014
12.0.2480RTM CU6February 16, 2015
12.0.2495RTM CU7April 20, 2015
12.0.2546RTM CU8June 19, 2015
12.0.2553RTM CU9August 17, 2015
12.0.2556RTM CU10October 19, 2015
12.0.2560RTM CU11December 21, 2015
12.0.2564RTM CU12February 22, 2016
12.0.2568RTM CU13April 18, 2016

Table 1: SQL Server 2014 RTM CU Builds

 

You can follow the KB article link below to see all of the CU builds for both the SQL Server 2014 RTM and the SQL Server 2014 SP1 branches.

SQL Server 2014 Build Versions

Like I have done for other versions and branches of SQL Server, I decided to scan the hotfix list for all of the Cumulative Updates in the RTM 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 2014 features you are using.

Here are the fixes in the RTM branch:

SQL Server 2014 RTM Cumulative Update 1 (Build 12.0.2342), 114 total public hot fixes

FIX: Nonclustered index corruption may occur when you run a complex UPDATE statement together with a NOLOCK hint against a table in SQL Server

FIX: Threads are not scheduled evenly in SQL Server 2012 or SQL Server 2014 Standard Edition

A memory leak occurs when a SQL Server Native Client OLE DB provider application calls the Prepare method in SQL Server 2012 or SQL Server 2014

FIX: Slow performance in SQL Server when you build an index on a spatial data type of a large table in a SQL Server 2012 or SQL Server 2014 instance

FIX: Slow performance in SQL Server 2012 or SQL Server 2014 when you build an index on a spatial data type of a large table

FIX: Suboptimal execution plan is generated when you run a query in SQL Server 2012 or SQL Server 2014

Slow SQL Server performance and a memory leak after you apply Cumulative Update 3 for SQL Server 2012 Service Pack 1 or in SQL Server 2014

FIX: Slow performance in SQL Server 2012 or SQL Server 2014 when you build an index on a spatial data type of a large table

Slow performance or error messages are logged when you have a transactional replication publisher server in SQL Server 2012 or SQL Server 2014

FIX: Insufficient system memory error occurs when you try to create an index in a char, varchar, or nvarchar type column in SQL Server 2012 or SQL Server 2014

FIX: A small number of rows are sampled in SQL Server when table statistics are updated automatically or together with specified sampling rate

FIX: Performance problems occur when database lock activity increases in SQL Server 2012 or SQL Server 2014

FIX: Performance problems occur in NUMA environments during foreign page processing in SQL Server 2012 or SQL Server 2014

FIX: Access violation may occur when trigger query joins large dataset in deleted/inserted table and runs in parallel in SQL server 2012 or SQL Server 2014

FIX: Access violation when you create an index on a no-partitioned table in SQL Server 2014

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

FIX: Performance decreases after an ALTER INDEX…ONLINE operation is aborted in SQL Server 2012 or SQL Server 2014

 

SQL Server 2014 RTM Cumulative Update 2 (Build 12.0.2370), 47 total public hot fixes

FIX: Performance problems occur in NUMA environments during foreign page processing in SQL Server 2012 or SQL Server 2014

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: Loads or queries on CCI tables block one another in SQL Server 2014

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: Data loss in clustered index occurs when you run online build index in SQL Server 2012 or SQL Server 2014

FIX: Metadata corruption occurs when you update index statistics with INCREMENTAL=ON in SQL Server 2014

 

SQL Server 2014 RTM Cumulative Update 3 (Build 12.0.2402), 31 total public hot fixes

FIX: Poor performance when you use table variables in SQL Server 2012 or SQL Server 2014

FIX: “Non-yielding scheduler” error when you insert or update many rows in one transaction in SQL Server 2014

FIX: Log Reader Agent crashes during initialization when you use transactional replication in SQL Server

FIX: Cannot create indexed view on a clustered columnstore index and BCP on the table fails in SQL Server 2014

FIX: Auto-statistics creation increases the compilation time for natively compiled stored procedure in SQL Server 2014

FIX: LCK_M_SCH_M occurs when you access memory-optimized table variables outside natively compiled stored procedures

FIX: An access violation occurs when you use UPDATE STATISTICS on a computed column in SQL Server 2014

 

SQL Server 2014 RTM Cumulative Update 4 (Build 12.0.2430), 54 total public hot fixes

FIX: “Cannot insert duplicate key” error occurs when you update a table by using a bitmap index that contains a partition key in SQL Server

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

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

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

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

FIX: Error 7105 when you execute UPDATE on sparse column set while ONLINE INDEX REBUILD is running on the same table in SQL Server

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

 

SQL Server 2014 RTM Cumulative Update 5 (Build 12.0.2456), 48 total public hot fixes

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

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

FIX: “System objects not be updated” when you use AlwaysOn Availability Groups in SQL Server 2012 or SQL Server 2014

FIX: Error occurs when you open FILESTREAM data that is hosted on dynamic disks in SQL Server 2012 or SQL Server 2014

FIX: SQL Server crashes when you retrieve an SQL statement hash for logging XEvent in SQL Server 2012 or SQL Server 2014

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

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

FIX: “No exceptions should be raised by this code” error when you run parallel query in SQL Server 2014

FIX: Non-yielding error occurs when you execute DML statements in SQL Server 2014

FIX: Error 35377 occurs when you try to access clustered columnstore indexes in SQL Server 2014

FIX: “Non-yielding scheduler” error and AlwaysOn Availability Group transitions to RESOLVING state not Failover

 

SQL Server 2014 RTM Cumulative Update 6 (Build 12.0.2480), 54 total public hot fixes

FIX: Sequence object generates duplicate sequence values when SQL Server 2012 or SQL Server 2014 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

Large query compilation waits on RESOURCE_SEMAPHORE_QUERY_COMPILE in SQL Server 2014

FIX: The transaction isolation level is reset incorrectly when the SQL Server connection is released in SQL Server 2014

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: Access violation occurs when you delete rows from a table that has clustered columnstore index in SQL Server 2014

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

FIX: OS error 665 when you execute DBCC CHECKDB command for database that contains columnstore index in SQL Server 2014

FIX: Memory leak occurs when you run DBCC CHECKDB against a database in SQL Server 2014

FIX: AlwaysOn availability groups are reported as NOT SYNCHRONIZING

FIX: Error 8646 when you run DML statements on a table with clustered columnstore index in SQL Server 2014

 

SQL Server 2014 RTM Cumulative Update 7 (Build 12.0.2495), 41 total public hot fixes  

FIX: A query that requires nested loops join takes longer to complete in SQL Server 2014

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

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

An AlwaysOn secondary replica crashes or raises error 3961 when the AlwaysOn database has CLR UDT in SQL Server 2014

FIX: Access violation and “No exceptions should be raised by this code” error occur when you use SQL Server 2012 or SQL Server 2014

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

FIX: Improved memory management for columnstore indexes to deliver better query performance in SQL Server 2014

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

FIX: A SELECT query run as a parallel batch-mode scan may cause a deadlock situation in SQL Server 2014

FIX: Error 3624 occurs when you execute a query that contains multiple bulk insert statements in SQL Server 2014

 

SQL Server 2014 RTM Cumulative Update 8 (Build 12.0.2546), 38 total public hot fixes  

An “Access Violation” occurs when you connect to an instance of SQL Server 2014

FIX: Partial results in a query of a clustered columnstore index in SQL Server 2014

FIX: SQL Server may shut down when you use Service Broker in SQL Server 2012 or SQL Server 2014

FIX: Access violation occurs when you query against a table that contains column store indexes in SQL Server 2014

Incorrect results are returned in an indexed view after an Insert or Delete operation on the base table occurs in SQL Server 2014

FIX: Error 33294 occurs when you alter column types on a table that has clustered columnstore indexes in SQL Server 2014

“Non-yielding Scheduler” error occurs and SQL Server stops responding when a database has columnstore indexes on a Microsoft SQL Server 2014 instance

FIX: Poor performance when a query contains table joins in SQL Server 2014

FIX: An I/O error that occurs on BPE files causes SQL Server 2014 to disable BPE

FIX: Paging out in memory occurs when columnstore index query consumes large memory in SQL Server 2014

FIX: Cannot create statistics when you execute statement to create it directly or in the background in SQL Server 2014

SQL Server in-place upgrade fails if the there’s no JOBS folder

FIX: Error 35377 when you run a parallel query that contains MERGE statements in SQL Server 2014

 

SQL Server 2014 RTM Cumulative Update 9 (Build 12.0.2553), 30 total public hot fixes  

FIX: An AlwaysOn database on a secondary replica shows a “NOT SYNCHRONIZING” status in SQL Server 2014

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

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

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

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 or 2014

FIX: Access violation occurs when you execute a query that contains many joins and coalesce functions in SQL Server 2014

FIX: Rare index corruption when you build a columnstore index with parallelism on a partitioned table in SQL Server 2014

FIX: A stalled dispatcher system dump occurs resulting in failover and service outage on the availability groups in SQL Server 2014

FIX: CMEMTHREAD waits occur when you execute many ad hoc queries in SQL Server 2012 or SQL Server 2014

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

 

SQL Server 2014 RTM Cumulative Update 10 (Build 12.0.2556), 31 total public hot fixes  

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

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

FIX: Sort operator spills to tempdb in SQL Server 2012 or SQL Server 2014 when estimated number of rows and row size are correct

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

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

FIX: CPU usage is higher than expected when you reduce the value of CAP_CPU_PERCENT argument in creating resource pool

FIX: Access violation when you query against a table that contains column store indexes in SQL Server 2014

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

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

 

SQL Server 2014 RTM Cumulative Update 11 (Build 12.0.2560), 18 total public hot fixes  

FIX: It takes a long time to install service packs or cumulative updates in SQL Server 2014

FIX: Assertion failure occurs when you run a query in SQL Server 2014

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

FIX: Access violation occurs when you use ODBC driver in SQL Server 2014

FIX: Access violation occurs when you update compressed data in SQL Server 2014

FIX: Incorrect result occurs when you run parallel query that contains many-to-many merge join in SQL Server 2014

FIX: “Non-yielding Scheduler” occurs when you query a partitioned table with column store index in SQL Server 2014

 

SQL Server 2014 RTM Cumulative Update 12 (Build 12.0.2564), 8 total public hot fixes 

FIX: Columnstore index corruptions occur when you use AlwaysOn Availability Groups feature in SQL Server 2014

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

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

 

SQL Server 2014 RTM Cumulative Update 13 (Build 12.0.2568), 31 total public hot fixes 

FIX: Column data may be deleted when you update another column in a table in SQL Server 2012

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

FIX: SELECT…INTO statement retrieves incorrect result from a clustered columnstore index in SQL Server 2014

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

Query plan generation improvement for some columnstore queries in SQL Server 2014

FIX: SQL Server 2014 crashes when you execute a query that contains a NONEXISTENT query hint on an In-Memory OLTP database

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

 

The reason that I put these lists together is that I want to convince more people to try to keep their SQL Server instances up to date with Cumulative Updates. If you do the proper testing, planning and preparation, I think the risks from installing a SQL Server Cumulative Update are quite low (despite the occasional issues that people run into).

If you install a Cumulative Update or Service Pack on a Production system the day it is released, after doing no testing whatsoever, and then run into problems (and don’t have a plan on how to recover), then I don’t have that much sympathy for you. 

On the other hand, if you go through a thoughtful and thorough testing process, and you have a plan for how you will install the CU, and how you would recover if there were any problems, then you are much less likely to have any problems. You are also much more likely to avoid the issues that are fixed by all of the included fixes in the new build of SQL Server. You have done your job as a good DBA.