SQL Server Diagnostic Information Queries for June 2015

This month, I have added a new, separate version of these queries for SQL Server 2016. This version will be improved with more SQL Server 2016-specific  queries and columns in existing queries in the near future.

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 two months ago, and so far, I have not heard any 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 2016, 2014 and 2012:

SQL Server 2016 Diagnostic Information Queries (June 2015)

SQL Server 2016 Blank Results

SQL Server 2014 Diagnostic Information Queries (June 2015)

SQL Server 2014 Blank Results

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

Interesting Upcoming Intel Processors

There has been quite a bit of leaked news and rumors about several future Intel processor families over the past couple of weeks, from what I consider to be pretty reliable sources. I’ll start out with the desktop and mobile processors and then move to the server processors.

Right now, it is a little unclear when the 14nm Intel Broadwell desktop processors will be released. These are a Tick release, built on the current Haswell microarchitecture, that was originally supposed to come out in late 2014. There were stories of early yield problems with the 14nm manufacturing process that caused Intel to delay the release in the desktop space. Low-power, mobile Broadwell processors have been available for several months now. The Core i7 Broadwell-U was released in Q1 of 2015, and these are typically used in high-end Ultrabooks, even through they only have two physical cores (plus hyperthreading).

My guess is that we might see higher power, mobile Broadwell-H and desktop Broadwell-S processors in the June-July 2015 timeframe. These will be compatible with the existing desktop LGA1150 socket and Z97 chipset motherboards that Haswell and Haswell Refresh processors used. The rumored models include the Core i7-5775C and Core i5-5675C. Personally, I plan on skipping Broadwell on the desktop, and waiting for Skylake.

Also in the mobile and desktop space (which is a useful preview of upcoming server processor families), there is news of the upcoming 14nm Skylake family being released in the August-September 2015 timeframe. Skylake is a Tock release (meaning a new microarchitecture) that will require a new LGA-1151 socket and a new Z170 chipset for enthusiast desktop machines. Supposedly, the unlocked enthusiast Skylake-S desktop processors (Core i7-6700K and Core i5-6600K) will be released sometime in August 2015. These are supposed to have at least 10% better performance than the current Haswell Refresh “Devil’s Canyon” processors in that same segment (Core i7-4790K and Core i5-4690K), even though the new processors will have lower power consumption and slightly lower clock speeds.

The mainstream Skylake-H for laptops is due for release in September of 2015, so if you are thinking about a new laptop, you might want to wait a few months. I definitely plan on building at least one Skylake-S desktop, pretty much as soon as they are available.

In the server processor space, there is a lot of recent new information. Back on May 5, 2015, Intel announced the Xeon E7 v3 family (Haswell-EX) that I talked about here. Next out of the gate will be the Xeon E5-4600 v3 family (Haswell-EP for four-socket servers), probably in Q4 2015, which I don’t think will be a good choice for SQL Server usage. This is because of the relatively poor scaling that I have seen in benchmark results for the earlier E5-4600 family processors. If you need to have a four-socket database server, a modern Xeon E7 v3 is a much better choice.

A more interesting introduction will be the 14nm Xeon E5-2600 v4 family (Broadwell-EP for two-socket servers), which will probably show up in Q1 or Q2 of 2016. This will be a Tick release, building on the Haswell microarchitecture that will have up to 22 physical cores and DDR4 2400 support. This processor should work in existing model servers such as the Dell PowerEdge R730.

Later in 2016, we should see the 14nm Xeon E7-4800/8800 v4 family (Broadwell-EX for four and eight-socket servers) that will have up to 24 physical cores.

Finally, in 2017, we should see a new 14nm Skylake server CPU that may merge the E5 and E7 lines into a single family, with up to 28 physical cores that will be part of the Purley platform which is detailed here and in Figure 1 below.

As these new processor families are released, with ever higher physical core counts, I really hope that Intel continues to have lower core count, “frequency-optimized” SKUs, with higher clock speeds and much lower SQL Server license costs.

 

Figure 1: Intel Server Platform Roadmap

Presenting Hardware 201 for the Denver SSUG

I did a live presentation at the May 21, 2015 meeting of the Denver SQL Server User’s Group, which was held at the newly remodeled Microsoft office in the Denver Tech Center.

Here is the abstract for my presentation:

Hardware 201: Selecting and Sizing Database Hardware for OLTP Performance

The foundation of database performance is the underlying server hardware and storage subsystem. Even the best designed and optimized database application can be crippled by an inadequate hardware and storage infrastructure. Recent advances in new processors and chipsets, along with improvements in magnetic and SSD storage have dramatically changed the evaluation and selection process compared to the past. Many database professionals struggle to keep up with new technology and often simply let someone else make their hardware selection and sizing decisions. Unfortunately, the DBA usually gets the blame for any performance issues that crop up later. Don’t let this happen to you! This session covers current and upcoming hardware from Intel and gives you the tools and resources to make better hardware selection decisions to support SQL Server OLTP workloads.

Here is a link to the PDF version of my presentation.

I was actually a last-minute substitution speaker for this meeting, and I was happy that I could help!

We actually did get a big turnout for the event. The Denver SSUG does a good job with their meetings, and they are a lot of fun. It is always a good idea to try to attend your local user’s group meetings, since you are likely to learn something and it is a very good opportunity to network.

If you are a SQL Server UG organizer, you might be interested in knowing that SQLskills has a number of speakers that regularly do remote presentations for user groups around the world. If you are interested in having one of us speak remotely at your event, please drop an e-mail to paul@sqlskills.com

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.