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

SQL Server 2014 RTM CU4

Microsoft has released SQL Server 2014 RTM CU4, which is Build 12.0.2430. This cumulative update has 54 hotfixes in the public fix list, which is a fairly large number. As usual, I think you should take a close look at the list of hotfixes, and then think pretty seriously about going through the testing and planning needed to get this CU deployed on your servers.

If you are working on a new SQL Server 2014 instance, I think it is almost a no-brainer to deploy CU4 before you go to Production.

Getting the Best Performance From an Intel DC P3700 Flash Storage Card

I recently had the opportunity to work on a new Dell PowerEdge R720 system that has two, 2TB Intel DC P3700 PCIe Flash Storage Cards installed. This particular card is the largest capacity model of the high-end P3700 series (Intel has lower-end P3600 and P3500 cards in this same family). As with most flash storage, larger capacity devices typically have much better performance than lower capacity devices from the same product family because there are more NANDs to read and write to and there are more channels to use.

Initially, I was somewhat disappointed by the CrystalDiskMark results for this device, as shown in Figure 1. These results are not terrible, especially compared to most SANs or a single 6Gbps SAS/SATA SSD, but they were not nearly as good as I was expecting.

It turns out that Windows Server 2012 R2 has native NVMe support, with some generic, default drivers. These drivers let Windows recognize and use an NVMe device, but they do not give the best performance. Installing the native Intel drivers makes a huge difference in performance from these cards.

You will need to download and install the drivers first (which will require a reboot), and then you will want to download and install the Intel Solid State Drive Data Center Tool (which is a command-line only tool), so you can check out the card and update the firmware if necessary. The links for those two items are below:

Intel Solid-State Drive Data Center Family for PCIe Drivers
Intel Solid-State Drive Data Center Tool

You should also confirm that you are using the Windows High Performance Power Plan and that your BIOS is not using any power management settings that affect the voltage supplied to the PCIe slots in your server. Setting the BIOS power management to OS control or high performance is usually what you need to do, but check your server documentation.

clip image002 thumb Getting the Best Performance From an Intel DC P3700 Flash Storage Card

Figure 1: CrystalDiskMark Results with Default Microsoft Driver

Here are the relevant results in text form:

Sequential Read :   682.778 MB/s
Sequential Write :   700.335 MB/s
        
Random Read 4KB (QD=32) :   381.311 MB/s [ 93093.6 IOPS]
Random Write 4KB (QD=32) :   282.259 MB/s [ 68910.9 IOPS]

After installing the native Intel drivers and updating the firmware, CrystalDiskMark looks much better! This is SAN-humbling performance from a single PCIe card that is relatively affordable.

clip image0025 thumb Getting the Best Performance From an Intel DC P3700 Flash Storage Card

Figure 2: CrystalDiskMark Results with Native Intel Driver

Here are the relevant results in text form:

Sequential Read :  1547.714 MB/s
Sequential Write :  2059.734 MB/s
        
Random Read 4KB (QD=32) :   646.816 MB/s [157914.2 IOPS]
Random Write 4KB (QD=32) :   419.740 MB/s [102475.6 IOPS]

This is a pretty dramatic difference in performance and it is another reason why database professionals should be paying attention to the details of their hardware and storage subsystem. Little details like this are easy to miss, and I have seen far too many busy server administrators not notice them.

Setting Your Page Verify Database Option to CHECKSUM

One thing I still run into quite often are SQL Server 2005 and newer databases that have their Page Verify database option set to TORN_PAGE or NONE. The most common reason for this is that an older database that was originally created in SQL Server 2000 or older was upgraded to SQL Server 2005 or newer, and the Page Verify Option was left at the older and less effective TORN_PAGE value. I also run into instances where people have changed the Page Verify database option to NONE, thinking that this would have a dramatic beneficial effect on performance (which is not true).

From BOL: “When CHECKSUM is enabled for the PAGE_VERIFY database option, the SQL Server Database Engine calculates a checksum over the contents of the whole page, and stores the value in the page header when a page is written to disk. When the page is read from disk, the checksum is recomputed and compared to the checksum value that is stored in the page header. This helps provide a high level of data-file integrity.”

Paul Randal talked about some of the myths around page verify here. Kendra Little wrote a good post that demonstrates how CHECKSUM reacts to corruption here.

In my opinion, all of your databases should be using CHECKSUM for their Page Verify database option. You can easily query sys.databases to find out the status of the Page Verify database option for all of your databases with this query:

-- Get value of page verify option for all databases
SELECT name, page_verify_option_desc
FROM sys.databases;

 

If you have just a few databases, it is pretty easy to run code like this for each one, to change this option:

-- T-SQL to change Page Verify option to CHECKSUM for a single database
USE [master]
GO
ALTER DATABASE [AdventureWorks] SET PAGE_VERIFY CHECKSUM  WITH NO_WAIT;
GO

 

If you have a large number of databases that need to be changed, you can write a query to generate the ALTER DATABASE statements for you, like this:

-- Generate ALTER DATABASE statements to change Page Verify option to CHECKSUM
SELECT N'ALTER DATABASE [' + db.name + N'] SET PAGE_VERIFY CHECKSUM  WITH NO_WAIT;'
FROM sys.databases AS db 
WHERE db.page_verify_option_desc <> N'CHECKSUM';

 

After you run this query, you can copy the rows from your results grid in SSMS to a new query window, and then run your ALTER DATABASE statements when you are ready, without having to write all of the T-SQL code yourself.

Keep in mind that just changing the setting to CHECKSUM does not instantly add CHECKSUMs to your existing data pages in the database. In order for this to happen, you have to read each page into memory, make some sort of change and then write it back out to the storage subsystem. This can happen from normal INSERT/UPDATE/DELETE activity over time, or from rebuilding your indexes.

Here are some useful links on this subject:

Checksum in SQL2005

How to tell if the IO subsystem is causing corruptions?

Inside The Storage Engine: Does turning on page checksums discard any torn-page protection?

Checksum and tempdb

Performance impact of enabling page checksum and default trace

Analyzing I/O Performance from SQLSaturday #300

I had the opportunity to present Analyzing I/O Performance at SQLSaturday #300 in Kansas City, MO on September 13, 2014. I think the session went well, judging by the amount and type of questions that I got during and after the presentation, along with the written feedback forms that I read afterwards.

The overall event was well-run, and well-attended, with some good barbeque for lunch. I have a lot of respect for the organizers and volunteers for SQLSaturday events.

You can get a PDF version of my deck here, and the queries that I ran here.

One side benefit of this event was a chance to drive my red Tesla back and forth between Parker, CO and Kansas City, MO, using the free Tesla Supercharger network (and a 50 amp circuit at my sister’s house in Topeka, KS). I talk a little about this trip in these two blog posts:

Tesla Road Trip to SQLSaturday #300 in Kansas City

Tesla Model S Road Trip Results

SQL Server Diagnostic Information Queries for September 2014

I revised a number of the queries this month in all five versions of the script. I have also added several new queries to the SQL Server 2012 and SQL Server 2014 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.

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.

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!

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

SQL Server 2012 SP1 CU12 and SP2 CU2 Released

Microsoft has released SQL Server 2012 Service Pack 1 CU12 (Build 11.0.3470) and SQL Server 2012 Service Pack 2 CU2 (Build 11.0.5548). Both of these CUs (which were released on the same day) have a significant number of valuable hotfixes. SP1 CU12 has 14 hotfixes, and SP2 CU2 has 50 hotfixes.

Needless to say, I think you should be planning on getting to the SQL Server 2012 SP2 branch if you are not already there. You should not just go to SP2 RTM (Build 11.0.5058), but all the way to SP2 CU2.

I am hoping that more and more of the CU-averse individuals and organizations will be convinced to start to install SQL Server Cumulative Updates after seeing this new Microsoft KB article:

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

Intel Xeon E5-2600 v3 Product Family and SQL Server

Intel finally announced their latest 22nm Xeon E5 v3 Product Family (Haswell-EP) today, which includes 27 different processor models (SKUs) for both one and two-socket servers. These SKUs go from four-core models, all the way up to eighteen-core models. This is an Intel Tock release, meaning a new microarchitecture, but still using the 22nm manufacturing process. We have previously seen the release of Haswell in the mobile space, mainstream desktop and enthusiast desktop space, so now it is time for one and two-socket servers to get Haswell-EP.

With SQL Server 2012/2014 Enterprise Edition, you must use core-based licensing, with a minimum of four physical cores per processor. Each one of those core licenses is relatively expensive, so you want to get the highest performance possible out of each physical core. When you are selecting a processor for SQL Server 2012/2014 it is foolish, false economy to select a lower-end, slower processor (with the same core-count) as a higher-end processor (with the same core count) in order to save a fairly small amount of money on hardware costs. Microsoft charges the same per core license cost regardless of the performance of the core.

Table 1 shows the “best” processor models for SQL Server, at the different physical counts, where you would get the best performance for a given core count. Keep in mind, as you go down in your core count per processor, from 18 to 16 for example, you would be saving twice that amount in core license costs with a two-socket server with both processor sockets populated. Saving the cost of four Enterprise Edition core licenses would pretty much pay for the base hardware cost of a nicely equipped server (not including any high-end flash storage).

Processor Cores Base Clock Turbo Clock L3 Cache
E5-2699 v3 18 2.3GHz 3.6GHz 45MB
E5-2698 v3 16 2.3GHz 3.6GHz 40MB
E5-2697 v3 14 2.6GHz 3.6GHz 35MB
E5-2690 v3 12 2.6GHz 3.5GHz 30MB
E5-2660 v3 10 2.6GHz 3.3GHz 25MB
E5-2667 v3 8 3.2GHz 3.6GHz 20MB
E5-2643 v3 6 3.4GHz 3.7GHz 20MB
E5-2637 v3 4 3.5GHz

3.7GHz 15MB

Table 1: Selected Intel Xeon E5-2600 v3 Processor Specifications

 

Intel claims that the Haswell-EP processors have an improved Turbo Boost, so that they will spend more time with more cores running close to or at full Turbo clock speed. One processor model I really like, especially for budget-minded organizations is the six-core E5-2643 v3, which has a very high Base and Turbo Clock speed, along with 20MB of L3 cache (the same as the eight-core E5-2667 v3).

You also don’t want to forget that both SQL Server 2012 and SQL Server 2014 Standard Edition still have an artificially low core count restriction of four sockets or 16 physical cores (whichever is lower). SQL Server 2012 Standard Edition has a RAM limit of 64GB for the database Engine, while SQL Server 2014 Standard Edition has a RAM limit of 128GB. These limits are all per instance, not per server.

These processors require new model servers, since they are not electrically or physically compatible with the preceding E5-2600 or E5-2600 v2 Product Families. All of the major server vendors have also announced new models that will use the Haswell-EP processor.

Small Enhancement to Microsoft SQL Server CU Knowledge Base Articles

Microsoft has made a small, but helpful improvement to the format for their Knowledge Base articles that accompany new Cumulative Updates for SQL Server 2012 and SQL Server 2014. As you hopefully know, there will not be any more cumulative updates for SQL Server 2008 or SQL Server 2008 R2, since those versions are now out of mainstream support.

There is now a new column in the hotfix table that lists the major functional area that the hotfix applies to (see Figure 1). Having this information readily available and visible can help you focus your efforts as you scan the overall hotfix list looking for relevant fixes for the SQL Server  components you are using, which is something you should be doing when each new Cumulative Update is released.

The fix area information is generated automatically (probably by VSTS), so it may not be 100% accurate, but it is certainly a good start.

clip image001 thumb Small Enhancement to Microsoft SQL Server CU Knowledge Base Articles

Figure 1: Recent SQL Server 2014 CU KB Article

SQL Server 2014 RTM Cumulative Update 3

Microsoft has released (about a week early by my calendar) SQL Server 2014 RTM Cumulative Update 3, which is build 12.0.2402. It has 32 fixes in the public fix list. As always, I think that you should strongly consider applying this update to your SQL Server 2014 instances, after you have done your normal round of application testing.

Based on some of the public hints that Microsoft has made, they may be moving away from using Service Packs to service SQL Server in the future, so I think it makes even more sense to get yourself and your organization ready to consider applying Cumulative Updates in order to maintain your SQL Server instances. This means having some sort of formal testing process for your applications and a plan for how you go about patching your SQL Server machines to minimize your downtime and reduce your risk.