The TPC-E OLTP benchmark is primarily limited by processor performance, assuming that you have enough I/O capacity in terms of IOPS and sequential throughput to properly drive the workload for the benchmark process. For official TPC-E submissions from a hardware vendor, this is a pretty safe assumption, given the cost and time required to submit test results to TPC.

SQL Server 2012 Enterprise Edition is licensed by the physical processor core instead of by the physical socket unlike previous versions of SQL Server. This means that it is even more important to carefully choose which processor to use for your database server. You want to get the best single-threaded performance possible for each physical processor core, so that you get the full value of your SQL Server 2012 core licenses. Choosing poorly when it comes your processor choice, and choosing poorly when it come to choosing between a two socket server and a four socket server can cause you to spend a lot more money on SQL Server 2012 Enterprise Edition licenses and actually get less performance.

This means that it makes sense to look at actual TPC-E scores divided by the total number of physical processor cores in the database server to get an idea about which processor will give you the most OLTP performance for each SQL Server 2012 core license.

Because of the way that Intel develops and releases processors to adhere to their “Tick-Tock” development schedule, and because of the more frequent processor revisions for the higher volume two socket space, a modern two socket server with the Intel Xeon E5-2690 processor is pretty much unbeatable for TPC-E performance from a score per core perspective.

Looking at Table 1, you can see a system with a Xeon E5-2690 having a 116.99 Score/Core rating, followed pretty closely by a previous generation Xeon X5690 with a 107.01 Score/Core. Even the older Xeon X5570 does quite well with a 102.14 Score/Core.

The best four socket system by this calculation is a Xeon X7560, coming in at a 63.97 Score/Core, followed pretty closely by a newer Xeon E7-4870 system with a 61.35 Score/Core. It seems that those two extra cores in a Westmere-EX are not giving as much as an advantage over the older Nehalem-EX as you would expect for TPC-E.

Table 1: TPC-E Performance by Processor

As I have said before, it is really hard to argue against choosing a modern, two socket database server for most OLTP workloads. The latest two socket Sandy Bridge-EP systems can support up to 384GB of RAM (with affordable 16GB DIMMs) and have PCI-E 3.0 support, with a pretty high number of PCI-E expansion slots. This should be more than enough CPU, memory, and I/O capacity for most workloads.

About a month ago, IBM submitted the first TPC-E OLTP benchmark result for SQL Server 2012 on a System x3650 M4, with a score of 1863.23 TpsE. This is for a two socket system with 2.9GHz Intel Xeon E5-2690 processors, 512GB of RAM, and 92 spindles.

If you divide the TpsE score by the number of physical cores in each system, you get a result of 57.68 for the Xeon E7-8870 system, and 116.45 for the Xeon E5-2690 system. This shows that the Xeon E5-2690 is capable of about twice the TpsE performance per physical core compared to the Xeon E7-8870. This is very significant for the new SQL Server 2012 core-based licensing, where you are paying five times the SQL Server license cost to get 2.47 times the TpsE score in this case. This is yet more evidence that the Xeon E5-2690 does extremely well on OLTP workloads!

This score is roughly comparable to an older score of 1284.14 TpsE for an HP DL380 G7 two socket database server, equipped with two 32nm 3.46GHz Intel Xeon X5690 processors, 192GB of RAM, and 84 spindles.

The AMD Opteron 6282SE has 16 physical cores, so that a two socket system will have 32 physical cores. The Intel Xeon X5690 has six physical cores (plus hyper-threading), so a two socket system will have 12 physical cores or 24 logical cores with hyper-threading enabled.

This is very grim news for AMD with the upcoming core-based licensing in SQL Server 2012 Enterprise Edition, which would make the SQL Server 2012 core licenses for the DL385 G7 system cost 2.66 times as much as for the DL380 G7 system. That will be a pretty hard sell to justify that much extra in licensing costs, for no extra performance or scalability.

The TPC-E benchmark has results going back to 2007, with a number of different processor families and generations represented from both Intel and AMD (although there are only a few results for systems with AMD processors, which tells you something pretty significant). This means that you can probably find a submitted system that has very similar set of processors, if not an exact match for an existing or a planned system that you want to compare.

The first benchmark result is for an IBM System x3850 X5 submitted on June 27, 2011, that has a score of 2,862.61 tpsE. This is for a four socket server, with the 10-core, 2.4GHz Intel Xeon E7-4870 processor. With hyper-threading enabled, this gives you 80 logical processors, along with 1TB of RAM. This system has a total of 105 spindles, primarily using Direct Attached Storage (DAS), which is not an insane amount by any means. Especially since they are only 10K SAS drives in six RAID 5 arrays… It is also using an 11.6TB initial database size.

The second benchmark result is for a Fujitsu PRIMEQUEST 1800E2 submitted on July 27, 2011, that has a score of 4,414.79 tpsE. This is for an eight socket server, with the 10-core, 2.4GHz Intel Xeon E7-8870 processor. With hyper-threading enabled, this gives you 160 logical processors, along with 2TB of RAM. This system has a total of 360 spindles, using Direct Attached Storage (DAS). There are 360 64GB SLC SSDs, with RAID 5 for the data files and RAID 10 for the log file. It is also using an 18.4TB initial database size.

These are actually two pretty interesting benchmark submissions. The Fujitsu system is essentially twice the size of the IBM system (eight sockets vs. four sockets), with twice the RAM (2TB vs. 1TB), and what would seem to be a much more powerful I/O subsystem (SLC SSDs vs. 10K SAS drives), with over triple the spindle count for the bigger system, along with RAID 10 for the log file.

Yet despite all of this, the eight socket system does not have a tpsE score that is twice as high as the four socket system.  A score of 4,414.79 tpsE is only 1.54 times higher than a score of 2,862.61 tpsE. This means we do not see linear scaling as we go from four sockets to eight sockets using the current top of the line Intel Xeon E7 processor.

This is probably a limitation of Non Uniform Memory Access (NUMA), since we did see much closer to linear scaling  going from two sockets to four sockets with this same Intel Xeon E7 family. There was a recent benchmark for an IBM System x3690 X5 system submitted on May 27, 2011, that has a score 0f 1,560.70 tpsE. This is for a two-socket server, with the 10-core, 2.4GHz Intel Xeon E7-2870 processor. With hyper-threading enabled, this gives you 40 logical processors, along with 512GB of RAM. A score of 2,862.61 tpsE is actually 1.83 times higher than a score of 1560.70 tpsE. This is much closer to linear scaling as you go from a two socket system to a four socket system.

What this means for you is that you would probably be better off (from an overall CPU capacity perspective) with two, four socket servers instead of one, eight socket server, assuming you can split or partition your workload between the servers. You would also be better off with two, two socket servers instead of one, four socket server.

The first benchmark is for a Futjitsu Primergy RX900 S2 system that comes in with a score of 4555.54 tpsE. This is for an eight socket server, with the 10-core, 2.4GHz Intel Xeon E7-8870 processor. With hyper-threading enabled, this gives you 160 logical processors, along with 2TB of RAM. This is the highest TPC-E score that has been submitted so far. While this is an impressive score, it is not four times the score of the two socket IBM system listed next.

The second benchmark is for an IBM System x3690 X5 system that has a score 0f 1,560.70 tpsE. This is for a two-socket server, with the 10-core, 2.4GHz Intel Xeon E7-2870 processor. With hyper-threading enabled, this gives you 40 logical processors, along with 512GB of RAM. This is essentially the same processor used in the eight socket Futjitsu system above, except that Intel has different SKUs for different socket capacities, (E7-2800 series, E7-4800 series, and E7-8800 series).

The third benchmark is for a HP ProLiant DL380 G7 Server that has a score of 1,284.14 tpsE. This is for a two-socket server, with the six-core, 3.46GHz Intel Xeon X5690 processor. With hyper-threading enabled, this gives you 24 logical processors, along with 192GB of RAM. This system uses the 32nm Westmere-EP, which still gives the best single-threaded OLTP performance due its higher clock speed (on the same basic Westmere architecture).

The table below shows the tpsE score per socket, and tpsE score per logical core for these three systems. It indicates that we don’t see linear scaling as we move from a two-socket system to an eight-socket system, using the new Westmere-EX, Xeon E7 series. It also shows better single-threaded performance for the Westmere-EP Xeon 5600 series (with a higher tpsE per logical core.

The TPC Benchmark E (TPC-E) is an OLTP performance benchmark that was introduced in February 2007. TPC-E is a not a replacement for the older TPC-C benchmark, but rather is a completely new OLTP benchmark. It is an OLTP, database-centric workload that is meant to reduce the cost and complexity of running the benchmark compared to the older TPC-C benchmark. It simulates the OLTP workload of a brokerage firm that interacts with customers using synchronous transactions and with a financial market using asynchronous transactions.

The business model of the brokerage firm is organized by Customers, Accounts, and Securities. The data model for TPC-E is significantly more complex, but more realistic than TPC-C, with 33 tables and many different data types. The data model for the TPC-E database does enforce referential integrity, unlike the older TPC-C data model.

The TPC-E database is populated with pseudo-real data, including customer names from the year 2000 U.S. Census, and company listings from the NYSE and NASDAQ. Having realistic data introduces data skew, and makes the data compressible. Unlike TPC-C, the storage media for TPC-E must be fault tolerant (which means no RAID 0 arrays). Overall, the TPC-E benchmark is designed to have reduced I/O requirements compared to the old TPC-C benchmark, which makes it both less expensive and more realistic since the sponsoring vendors will not feel as much pressure to equip their test systems with disproportionately large disk subsystems in order to get the best test results. The TPC-E benchmark is also more CPU intensive than the old TPC-C benchmark.

The TPC-E implementation is broken down into a Driver and a System Under Test (SUT), separated by a mandatory network. The Driver represents the various client devices that would use an N-tier client-server system, abstracted into a load generation system. The SUT has multiple Application servers (Tier A) that communicate with the database server and its associated storage subsystem (Tier B). TPC provides a transaction harness component that runs in Tier A, while the test sponsor provides the other components in the SUT.

The performance metric for TPC-E is transactions per second, tpsE. The actual tpsE score represents the average number of Trade Result transactions executed within one second. To be fully compliant with the TPC-E standard, all references to tpsE results must include the tpsE rate, the associated price per tpsE, and the availability date of the priced configuration.

It seems interesting that, as of early 2011, Microsoft is the only database vendor that has submitted any TPC-E results, even though the TPC-E benchmark has been available since early 2007. Whatever the reasons why other database vendors haven’t posted results, there are certainly many results posted for SQL Server, which makes it a very useful benchmark when assessing SQL Server hardware.

The most recent posted TPC-E result is for an HP Proliant DL580 G7 Server with a 2,454.51 tpsE score for a four-socket system. This system has four, ten-core Intel Xeon E7-4870 processors that have a total of 80 logical cores for the system. It also has 1TB of RAM and 1100 spindles in its I/O subsystem, using an 11TB initial database size for the test. Looking at the Executive Summary, you can see that it is running SQL Server 2008 R2 Enterprise Edition on top of Windows Server 2008 R2 Enterprise Edition SP1.

It is using RAID 10 for both the data files and log file, with (950) 72GB 6Gbps 15K SAS drives, (150) 146GB 6Gbps 15K SAS drives, and four 400GB SSDs.

Digging deeper into the Supporting Files for the submission, you can find how HP decided to configure their SQL Server 2008 R2 instance for the benchmark. There are some settings that I do not agree with (even for a benchmark), and several settings that I would never want to use in a production environment. The complete set is shown in Figure 1.

-- HP SQL Configuration Options for TPC-E Benchmark
-- Don't use these settings on a production server!
exec sp_configure 'show advanced options', '1'
reconfigure with override
go

exec sp_configure 'max server memory', 1038000
exec sp_configure 'recovery interval','32767'
exec sp_configure 'awe enabled','0'
exec sp_configure 'max degree of parallelism','1'
exec sp_configure 'lightweight pooling','1'
exec sp_configure 'priority boost', '1'
exec sp_configure 'max worker threads', 3000
exec sp_configure 'default trace enabled', 0
go
reconfigure with override
go

Figure 1: HP TPC-E SQL Configuration Settings

At any rate, I think it is interesting to poke around in the details of the TPC-E submissions.

After that effort, I was ready to write a few queries to see if anything interesting might reveal itself from the actual raw data. Since SQL Server is licensed by physical socket (when you buy a processor license), I thought it would be interesting to rank the results by TpsE per Socket, by simply dividing the TpsE score by the number of sockets. This gives you a pretty good idea of which processor does the best on that benchmark, assuming the rest of the system was properly optimized (which is probably a pretty safe assumption given the time and cost of doing an official TPC-E submission). This gives you an idea of which processor gives you the most “bang for the buck” on the TPC-E benchmark.

Looking at the results this way, the two systems at the top of the list both use the Intel Xeon X5680 processor. After that, the next seven systems use the Intel Xeon X7560 processor, and they show pretty good scaling as you go from four sockets to eight sockets (i.e. the eight socket scores are pretty close to double the four socket scores for that processor), which shows the effectiveness of NUMA. Next in line is a two socket AMD Opteron 6176 SE system, which does about 10% better than a trio of Intel Xeon X5570 systems that are about a year older than the AMD Opteron 6176 SE system. The next four systems are a mix of Intel “Nehalem” and AMD “Magny Cours” based systems that conclude the top tier of results in this query. The lower limit of this “top tier” is shown in green in the table below.

The interesting thing is how much of a drop we see with the next several systems that use the older, six core Intel Xeon X7460 (which was no slouch in its day), showing a drop of nearly 50% in the TpsE per socket score compared to the lowest system in the top tier. Even more interesting, is the difference between the four socket Intel Xeon X7460 systems and the four socket Intel Xeon X7560 systems, which shows what a huge improvement the Intel “Nehalem” is compared to the older Intel “Dunnington”. You can also see that the newer two socket Intel systems (X5680 and X5570) do much better than the older four socket X7460 systems, in both absolute and per socket terms. The two listed sixteen socket Intel Xeon X7460 systems do quite poorly, showing the weakness of the old shared front-side bus architecture in older Intel Xeon processors.

My takeaway from this relatively simple analysis is that any system with processors older than the Intel “Nehalem” or the AMD “Magny Cours” (Intel 55xx, Intel 75xx or AMD 61xx) will be pretty severely handicapped compared to a system with a “modern” processor. This is especially evident with older Intel four socket systems that use Xeon 74xx or older processors, which are easily eclipsed by two socket systems with Intel Xeon X5570 or X5680 processors.  This analysis also shows how well the 32nm Intel Xeon X5680 “Westmere-EP” processor does on the TPC-E workload, which in my mind, also shows that it is an excellent processor for OLTP workloads in general.