SQL Server Diagnostic Information Queries for May 2015

I revised a number of the queries this month in just 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, including some more links to relevant blog post articles. 

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.  I started this policy last month, and so far, I have not heard many complaints.

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 versions of these queries for SQL Server 2014 and 2012:

SQL Server 2014 Diagnostic Information Queries (May 2015)

SQL Server 2014 Blank Results

SQL Server 2012 Diagnostic Information Queries (May 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!

Intel Xeon E7 v3 Product Family Released

On May 5, 2015, Intel announced the new E7 v3 Product Family, which is also known as the Haswell-EX, which is meant for four and eight-socket servers. This 22nm processor is a Tock release (which means that it is using a new processor microarchitecture compared to the previous 22nm Ivy Bridge-EX). An updated graphic that outlines the Tick-Tock development history for the Xeon 7xxx and E7 families is shown in Figure 1.

intel-xeon-tick-tock-model

Figure 1: Intel Tick-Tock Development Model

Intel only has 12 different processors in this product family, and of these, only four are really good choices from a licensing cost and performance perspective for SQL Server 2012 and newer. These are shown in Table 1.

Processor Cores Base Clock Turbo Clock L3 Size QPI Speed
E7-8893 v3 4 3.2 GHz 3.5 GHz 45 MB 9.6 GT/s
E7-8891 v3 10 2.8 GHz 3.5 GHz 45 MB 9.6 GT/s
E7-8867 v3 16 2.5 GHz 3.3 GHz 45 MB 9.6 GT/s
E7-8890 v3 18 2.5 GHz 3.3 GHz 45 MB 9.6 GT/s

Table 1: Preferred Xeon E7 v3 Family Processors for SQL Server 2012/2014/2016

I make this assertion because of how SQL Server 2012 and newer core-based licensing works. You pay for the number of physical cores in your system (on non-virtualized servers), and Microsoft does not care whether your processor cores are slow or fast, the license price is the same either way. Given this basic fact, you really should pick processors with faster cores (better single-threaded performance) at the same physical core count, to get the most performance possible for a given licensing cost. In some cases, you may be able get away with a lower core count processor that has faster individual cores, and still be able to support a similar overall total workload at a much lower SQL Server licensing cost.

The other eight processors in the E7 v3 Product Family all have lower specifications (at the same core count) than the four in my preferred list, so they are bad choices in my opinion. If you or someone else in your organization picks one of those other eight processors, you are not making a good choice. I am guessing that some people may argue that the slower processors at a given core count are less expensive, so they make sense in some situations.

My response would be that when you are talking about four or eight, high core count processors, the SQL Server 2012 and newer license costs are going to far outweigh the relatively small difference in the cost of a given processor. The single-threaded performance difference between a 2.1 GHz Xeon E7-8870 v3 ($7,175 each) and a 2.5 GHz Xeon E7-8890 v3 ($4,672 each) is likely to be about 20%, while the difference in total system cost will be much less than 20%.

You may notice that all of my recommended E7 v3 processors are E7-88xx processors, which means they are designed for eight-socket servers. That does not mean that you have to use them in an eight-socket server. You can actually use those E7-88xx processors in four-socket servers, which are much more common and much less expensive.

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 June 19, 2015, there have been eight 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, CU8 (Build 12.0.2546), which was released on June 19, 2015. 

Note: SQL Server 2014 Service Pack 1 was re-released on May 4, 2015, and SQL Server 2014 SP1 CU1 was released on June 19, 2015. SQL Server 2014 SP1 CU1 is synchronized with SQL Server 2014 RTM CU8, so I think it is now ok to migrate to the SQL Server 2014 SP1 branch after you have done your own application testing and are ready to make that move.

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

Build Description Release Date
12.0.2342 RTM CU1 April 21, 2014
12.0.2370 RTM CU2 June 27, 2014
12.0.2402 RTM CU3 August 18, 2014
12.0.2430 RTM CU4 October 21, 2014
12.0.2456 RTM CU5 December 17, 2014
12.0.2480 RTM CU6 February 16, 2015
12.0.2495 RTM CU7 April 20, 2015
12.0.2546 RTM CU8 June 19, 2015

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

 

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.

Beware of the Native Microsoft NVMe Driver!

Non-Volatile Memory Express (NVMe) is a specification for accessing solid-state drives (SSDs) attached through the PCI Express (PCIe) bus. It gives lower storage latency and much higher queue depths. Even though this technology is most relevant for server workloads, there are also some affordable new NVMe devices meant for client devices and workloads. A good primer on NVMe is available here.

The use case from nvmexpress.org is that

“NVM Express is architected from the ground up for Non-Volatile Memory (NVM). NVM Express significantly improves both random and sequential performance by reducing latency, enabling high levels of parallelism, and streamlining the command set while providing support for security, end-to-end data protection, and other Client and Enterprise features users need. NVM Express provides a standards-based approach enabling broad ecosystem adoption and PCIe SSD interoperability.”

NVMe is being pushed as a modern replacement for the old Advanced Host Controller Interface (AHCI) that most flash storage devices are still using, and all indications are that NVMe will really start to become more popular and more affordable in 2015/2016.

Windows Server 2012 R2 and Windows 8.1 have a native NVMe driver that allows NVMe devices to be automatically recognized by Windows. This driver works, but does not offer the best performance. I wrote about my experiences with the native NVMe driver last October. Microsoft has also released a hotfix to Windows Server 2008 R2 and Windows 7 that gives native NVMe support to the operating system.

Anandtech has had similar results with several different NVMe devices. Their information (from Samsung) was that

“the performance difference was due to the Microsoft NVMe driver creating FUA (Force Unit Access) I/O write commands. These FUA commands bypass the DRAM cache on the SSD and directly write to the flash, increasing the response time and also lowering bandwidth. For the same access traces, this situation does not happen with the Microsoft AHCI driver.”

This sounds pretty similar to the difference between write-back and write-through caching for RAID controllers. If you have any NVMe storage devices, you should make absolutely sure that you are using the vendor supplied NVMe driver rather than the generic Microsoft NVMe driver. My fear is that it will be very common for many server administrators to simply install their NVMe device, start the server, and then think everything is ok, since Windows recognized the device and it seems to be working.

There are a lot of recent tests of new NVMe storage devices to whet your appetite for this technology. Here are some reviews and tests of client devices:

PCIe SSD Roundup – Samsung SM951 NVMe vs. AHCI, XP941, SSD 750 and More!

Intel SSD 750 Review

Intel 750 series SSD review: Storage so fast, only the highest-end PCs can keep up 

Intel SSD 750 Series NVMe PCI Express SSD Review

Intel 750 Series 1.2TB NVMe PCIe SSD Review

Here are  some reviews of server devices:

Intel SSD DC P3700 Review: The PCIe SSD Transition Begins with NVMe

Intel SSD DC P3700 800GB and 1.6TB Review: The Future of Storage

Intel SSD DC P3700 Review (800GB) – NVMe for Enterprise…and Enthusiasts?

Hopefully, Microsoft will improve the performance of their native NVMe driver in a future update for Windows Server 2012 R2 and Windows 8.1. I certainly hope the native NVMe driver performs better in Windows 10 and “Windows Server 2016”. I would love to see Microsoft’s Jose Barreto weigh in on this subject!

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

As of July 20, 2015, there have been seven 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, I really think you should be running the latest SQL Server 2012 Service Pack and Cumulative Update. Right now, that means Service Pack 2, CU7 (Build 11.0.5623), which was released on July 20, 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. 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
     

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 1 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

 

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:

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.

SQL Server Diagnostic Information Queries for March 2015

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 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 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!

SQL Server 2012 SP2 CU5 Available

Microsoft has released SQL Server 2012 Service Pack 2 Cumulative Update 5, which is Build 11.0.5582. This Cumulative Update has 27 fixes in the public fix list. This is the build that you want to be on if you are running SQL Server 2012, since you should be on the SQL Server 2012 SP2 branch by now, at least in my opinion. There are a number of pretty significant fixes in this update.

Microsoft has also released SQL Server 2012 Service Pack 1 Cumulative Update 15, which is Build 11.0.3487. This Cumulative Update has only one hotfix in the public fix list. As I said above, if you are running SQL Server 2012, you really should be on the SP2 branch, even though SP1 is still supported.

There is no corresponding CU for SQL Server 2012 RTM, since SQL Server 2012 RTM is not a supported Service Pack. You really should not be on the SQL Server 2012 RTM branch anymore.

Balancing Your Available SQL Server Core Licenses Evenly Across NUMA Nodes

One issue I recently ran into with a customer was a case where they were using a new, two-socket server with two Intel Xeon E5-2697 v2 Ivy Bridge-EP processors. This particular 22nm processor has twelve physical cores, plus hyper-threading, so you can have 24 logical cores for one entire physical processor, and 48 logical cores for a two-socket server with both sockets populated.

The reason why we had an issue is because both SQL Server 2012 and 2014 Standard Edition have an artificial license limit of either 16 physical cores or 32 logical cores (along with a limit of four sockets, whichever is lower). This meant that SQL Server 2014 Standard Edition could only use 32 out of the 48 logical cores in this new server. To make matters worse, the available cores were spread unevenly across the two NUMA nodes in the server. There were 24 logical cores on one NUMA node and 8 logical cores on the other NUMA node in the server, which was not an ideal situation.

Fortunately, it is pretty easy to fix this issue by using the ALTER SERVER CONFIGURATION command that was introduced in SQL Server 2008 R2. On the negative side, fixing it was not completely intuitive, because of these dumb Standard Edition license limits.

You can use the queries in the code below to detect and correct this issue.

-- Balancing your available SQL Server core licenses evenly across two NUMA nodes
-- Glenn Berry
-- SQLskills.com


-- Get socket, physical core and logical core count from SQL Server Error Log
EXEC sys.xp_readerrorlog 0, 1, N'detected', N'socket';


-- SQL Server NUMA node information 
SELECT node_id, node_state_desc, memory_node_id, processor_group, online_scheduler_count, 
       active_worker_count, avg_load_balance, resource_monitor_state
FROM sys.dm_os_nodes WITH (NOLOCK) 
WHERE node_state_desc <> N'ONLINE DAC' OPTION (RECOMPILE);


-- SQL Server schedulers by NUMA node
SELECT parent_node_id, 
  SUM(current_tasks_count) AS [current_tasks_count], 
  SUM(runnable_tasks_count) AS [runnable_tasks_count], 
  SUM(active_workers_count) AS [active_workers_count], 
  AVG(load_factor) AS avg_load_factor
FROM sys.dm_os_schedulers WITH (NOLOCK) 
WHERE [status] = N'VISIBLE ONLINE'
GROUP BY parent_node_id;



-- SQL Server NUMA node and cpu_id information
SELECT parent_node_id, scheduler_id, cpu_id
FROM sys.dm_os_schedulers WITH (NOLOCK) 
WHERE [status] = N'VISIBLE ONLINE';


-- Fixing the problem

-- Unfortunately, this does not work, due to the license limits in SQL 2012/2014 Standard Edition
ALTER SERVER CONFIGURATION SET PROCESS AFFINITY NUMANODE = 0,1;

-- Msg 5833, Level 16, State 2, Line 7
-- The affinity mask specified is greater than the number of CPUs supported or licensed on this edition of SQL Server.


-- This command spreads your available 32 logical core licenses across two NUMA nodes 
-- This is valid for an Intel processor, with HT enabled
ALTER SERVER CONFIGURATION
SET PROCESS AFFINITY CPU = 0 TO 15, 25 TO 40;
 

 

A similar issue with “grandfathered” SQL Server 2012 Enterprise Server+CAL customers was discussed by my colleague, Jonathan Kehayias here.