For Day 24 of this series, I want to talk a little bit about the TPC-E OLTP benchmark.

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.