New Intel Data Center SSDs

Intel has announced two new series of their 2.5-inch and 1.8-inch SATA data center solid-state drives. These are the upper-tier DC S3710 Series and mid-level DC S3610 Series. The DC S3710 replaces the previous DC S3700 Series, which was introduced back in late 2012. The high-level specifications for the entire Intel Solid-State Drive Data Center Family are listed here.

The Intel SSD DC S3710 Series is a 2.5” form factor and comes in 200GB, 400GB, 800GB, and 1.2TB capacities. The Intel SSD DC S3610 Series comes in both 2.5” and 1.8” form factors with the 2.5” coming in 200GB, 400GB, 480GB, 800GB, 1.2TB, and 1.6TB capacities and the 1.8” coming in 200GB, 400GB, and 800GB capacities. Both of the new SSD Series will use a high-endurance version of Intel’s 20nm MLC NAND, with a SATA interface and will have greater write performance compared to the previous models. The endurance rating for the DC S3710 is 10 drive writes per day for the length of the five-year warranty, while the DC S3610 is rated at 3 drive writes per day for five years.

Intel quotes these performance figures for the DC S3710 Series:

  • Sustained sequential read/write
    • 200GB: Up to 550/300MB/s
    • 400GB: Up to 550/470MB/s
    • 800GB: Up to 550/460MB/s
    • 1.2TB: Up to 550/520MB/s
  • Random 4k read/write
    • 200GB: Up to 85,000/43,000 IOPS
    • 400GB: Up to 85,000/43,000 IOPS
    • 800GB: Up to 85,000/39,000 IOPS
    • 1.2TB: Up to 85,000/45,000 IOPS

Intel also quotes these performance figures for the 2.5-inch version of the DC S3610 Series:

  • Sustained sequential read/write
    • 200GB: Up to 550/230MB/s
    • 400GB: Up to 550/400MB/s
    • 480GB: Up to 550/440MB/s
    • 800GB: Up to 540/520MB/s
    • 1.2TB: Up to 500/500MB/s
    • 1.6TB: Up to 540/500MB/s
  • Random 4k read/write
    • 200GB: Up to 84,000/12,000 IOPS
    • 400GB: Up to 84,000/25,000 IOPS
    • 480GB: Up to 84,000/28,000 IOPS
    • 800GB: Up to 84,000/28,000 IOPS
    • 1.2TB: Up to 84,000/28,000 IOPS
    • 1.6TB: Up to 84,000/27,000 IOPS

The S3710 Series has better write performance, and higher write endurance compared to the S3610 Series. As always, the larger capacity models typically have better performance than the lower capacity models from the same series. These drives are supposedly available now, although I have not found them listed for sale anywhere just yet. Here is the suggested retail pricing from Intel:

  • 1.2TB  DC S3710            $1909.00
  • 800GB DC S3710            $1249.00
  • 400GB DC S3710            $  619.00
  • 200GB DC S3710            $  309.00

 

  • 1.6TB  DC S3610            $1719.00
  • 1.2TB  DC S3610            $1289.00
  • 800GB DC S3610            $  839.00
  • 480GB DC S3610            $  509.00
  • 400GB DC S3610            $  419.00
  • 200GB DC S3610            $  200.00

These drives are a very attractive alternative to being price-gouged for internal flash-storage by your server vendor. I have had a number of customers use the older DC S3700 drives in new servers they have purchased, all with good results.

Remote Presentation of Analyzing I/O Subsystem Performance for Israel PASS Chapter

I will be doing a remote presentation of Analyzing I/O Subsystem Performance for the Israel PASS Chapter on Monday, February 2. The meeting starts at 8:30AM Mountain Standard Time in the United States.

Here is the abstract for this presentation:

SQL Server is often I/O bound – but why? Do you feel lost when talking to your storage administrator? Are your storage subsystems like a mysterious black box where your databases live but you can’t go visit? This session will get you up to speed with the fundamentals of storage subsystems for SQL Server. You will learn about the different types of storage that are available, and how to decide what type of storage to use for different workload types. You will also learn useful tips and techniques for configuring your storage for the best performance and reliability. We’ll cover methods to effectively measure and monitor your storage performance so that you will have valuable information and evidence available the next time you have to discuss IO performance with your storage administrator. Come to this session to learn how to analyze I/Os as well as options to reduce the bottlenecks.

The free Eventbrite signup for this event is available here.

SQL Server 2012 SP2 CU4 Available

Microsoft has released SQL Server 2012 Service Pack 2 Cumulative Update 4, which is Build 11.0.5569. This Cumulative Update has 36 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.

Microsoft has also released SQL Server 2012 Service Pack 1 Cumulative Update 14, which is Build 11.0.3486. This Cumulative Update has only eight hotfixes in the public fix list. There is no corresponding CU for SQL Server 2012 RTM, since SQL Server 2012 RTM is not a supported Service Pack.

SQL Server Diagnostic Information Queries for January 2015

I revised a number of the queries this month in all five versions of the script.  The main enhancements are additional comments and documentation about the queries.

Here are the current query counts for each version:

SQL Server 2014         72 Queries

SQL Server 2012         69 Queries

SQL Server 2008 R2    65 Queries

SQL Server 2008         59 Queries

SQL Server 2005         51 Queries

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). 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 two latest Pluralsight courses, which are SQL Server 2014 DMV Diagnostic Queries – Part 1 and SQL Server 2014 DMV Diagnostic Queries – Part 2. Both of these courses are pretty short and to the point, at 67 and 77 minutes respectively. Part 3 of the series has been recorded, and will probably be published in February 2015.

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

SQL Server Diagnostic Information Queries for December 2014

I revised a number of the queries this month in all five versions of the script. 

Here are the current query counts for each version:

SQL Server 2014         72 Queries

SQL Server 2012         69 Queries

SQL Server 2008 R2    65 Queries

SQL Server 2008         59 Queries

SQL Server 2005         51 Queries

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). 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 two latest Pluralsight courses, which are SQL Server 2014 DMV Diagnostic Queries – Part 1 and SQL Server 2014 DMV Diagnostic Queries – Part 2. Both of these courses are pretty short and to the point, at 67 and 77 minutes respectively. Part 3 of the series has been recorded, and will probably be published in February 2015.

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 5

Microsoft has released SQL Server 2014 RTM Cumulative Update 5, which is Build 12.0.2456. This cumulative update has 47 fixes in the Public hotfix list.

This handy Microsoft KB article “SQL Server 2014 build versions”, lists all of the cumulative updates that have been released for SQL Server 2014, including this one. If you have not already done so, I strongly recommend that you take a look at this KB article “Recommended updates and configuration options for SQL Server 2012 and SQL Server 2014 used with high-performance workloads”, which is full of very good information about how to configure SQL Server 2012/2014, along with quite a few reasons that you should try to stay current with your SQL Server cumulative updates.

SQL Server 2012 SP2 Cumulative Update 3

Microsoft has released SQL Server 2012 SP2 Cumulative Update 3, which is Build 11.0.5556. This CU has 34 hotfixes in the public fix list. If you are running SQL Server 2012, this is the build you should be planning to get installed, after you have done your testing and planning for the installation.

They have also released SQL Server 2012 SP1 Cumulative Update 13, which is Build 11.0.3482. It only has 10 hotfixes in the public fix list. Personally, I think you should be planning to move from the SP1 branch to the SP2 branch sooner, rather than later. The RTM branch of SQL Server 2012 has been retired.

Presentation Materials From Fall 2014 SQL Intersection

I recently had the opportunity to present Analyzing I/O Performance and Dr. DMV’s Toolkit at the Fall 2014 SQL Intersection conference in Las Vegas. This is a smaller (but rapidly growing) conference that has hand-picked, top-tier speakers. The conference was a lot of fun, and I heard a lot of positive feedback about the speakers  from the attendees while I was there.

My content for Analyzing I/O Performance is here, and for Dr. DMV’s Toolkit is here.

SQL Server Diagnostic Information Queries for November 2014

I revised a number of the queries this month in all five versions of the script.  It was very nice to have so many people thank me for these queries during the PASS 2014 Conference!

Here are the current query counts for each version:

SQL Server 2014         72 Queries

SQL Server 2012         69 Queries

SQL Server 2008 R2    65 Queries

SQL Server 2008         59 Queries

SQL Server 2005         51 Queries

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). 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 want to understand how to better run and interpret these queries, you should consider listening to my latest Pluralsight course, which is SQL Server 2014 DMV Diagnostic Queries – Part 1. This course is short and to the point (only 67 minutes), and I think you will enjoy it!  Part 2 of this series is recorded, and will be showing up on Pluralsight relatively soon!

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

TPC-E Single-Threaded Performance Leaderboard

Fujitsu recently posted a new TPC-E benchmark result of 3777.08 for SQL Server 2014, using a two-socket server with the 18-core, 22nm Intel Xeon E5-2699 v3 (Haswell-EP) processor. This is the highest ever actual TPC-E score for a two-socket server, which sounds quite impressive on the surface.

One thing that I have been doing for years is to take the actual, raw TPC-E score, and divide it by the number of physical cores in the system (which is how SQL Server 2012/2014 is licensed on physical servers) to come up with a “Score/Core” figure as shown in Table 1. This simple calculation helps you evaluate the single-threaded performance of a particular processor, which is very relevant for OLTP workloads. Looking at the TPC-E results like this, the Intel Xeon E5-2699 v3  comes in at seventh place on the TPC-E Single-Threaded Performance Leaderboard. Why is this?

The server vendors (who put together these official TPC-E submissions) will always use the “top of the line” processor for a particular model server for one of these benchmark efforts. This top-level SKU is going to have the highest core count available from a particular CPU family and generation. Unfortunately, the highest core count processors from a particular CPU family and generation will run at lower base and turbo clock speeds than the lower core count, “frequency optimized” models from that same CPU family and generation. This means that the Score/Core result tends to decrease as the number of cores increases. This is partially offset by the architectural improvements that are added to each new generation processor, but those improvements usually don’t make up completely for the lower clock speeds.

So what relevance does this have for the average database professional?

Well, think about how much it would cost to purchase 36 processor core licenses for SQL Server 2014 Enterprise Edition. The answer is about $247,392.00, which is about ten times what a fully-loaded two-socket server would cost. If you were to choose the eight-core Intel Xeon E5-2667 v3 processor, with its much higher 3.2GHz base clock speed, it would only cost about $109,952.00 for the SQL Server 2014 licenses. You would also get probably 30-35% better single-threaded performance than with the 18-core model, while losing perhaps 35-40% of your total processor capacity.

If you are worried about total capacity, you could even buy a second server (if you could split your workload), and save enough on the license costs (32 core licenses vs. 36 core licenses) to pay for the second server. If you did this, you would have more total processor capacity, double the RAM, and much better OLTP performance. Remember, the actual raw TPC-E score is a gauge of the total processor capacity of the system, while the Score/Core helps you evaluate single-threaded processor performance.

I really wish the server vendors would take the relatively easy and inexpensive step of testing their benchmark configurations with different model processors. Once they had everything setup and tuned for the high-core count flagship processor, they could simply repeat the test runs and validation process for some of the more interesting lower core count “frequency optimized” processor models, and submit those results. TPC could help by listing the Score/Core results for all of the TPC-E benchmark submissions.

 

TpsE Score/Core System Processor Total Cores Sockets
1881.76 117.61 HP ProLiant DL380p Gen8 Intel Xeon E5-2690                                         16 2
1871.81 116.99 PRIMERGY RX300 S7 Intel Xeon E5-2690                                         16 2
1863.23 116.45 IBM System x3650 M4 Intel Xeon E5-2690                                         16 2
2590.93 108 IBM System x3650 M4 Intel Xeon E5-2697 v2 24 2
1284.14 107.01 HP ProLiant DL380 G7 Server Intel Xeon X5690                                  12 2
1268.3 105.69 PRIMERGY RX300 S6 12×2.5 Intel Xeon X5690                                  12 2
3777.08 104.92 PRIMERGY RX2540 M1 Intel Xeon E5-2699 v3 36 2
1246.13 103.84 PRIMERGY RX300 S6 Intel Xeon X5680                                  12 2
2472.58 103.02 PRIMERGY RX300 S8 Intel Xeon E5-2697 v2 24 2
817.15 102.14 IBM System x3650 M2 Intel Xeon X5570                                 8 2

Table 1: TPC-E Single-Threaded Performance Leaderboard