Blog Author:  Charles Levine, Principal Program Manager, SQL Server Performance Engineering

Maximizing SQL Server’s performance and scalability is a complex engineering challenge. Beyond good architecture and well-designed code, we also extensively measure SQL Server to ensure that it delivers the best performance compared to previous versions and other databases. But which benchmarks do we use? That’s an important question. Being super-fast on workloads that aren’t relevant for real-world applications may be good for bragging rights, but doesn’t help our customers.

We actually use many benchmarks to get a broad spectrum of coverage. In OLTP, though, our primary workload is the Transaction Processing Performance Council’s TPC-E benchmark [1]. The TPC is an industry standards organization that defines performance benchmarks. TPC-E exercises a range of complex database functions that are representative of real-world workloads.

In this post, I want to give you some insight into why we focus on TPC-E for our engineering work and why we’ve abandoned the better known, but now less relevant, TPC-C for published results and marketing.

TPC-C is 20 years old and has changed little since its introduction in 1992. All of the major DBMS companies have spent years picking through every detail of TPC-C. Databases have been optimized for TPC-C to such a degree that it long ago stopped driving customer-relevant engineering improvements.

In contrast, TPC-E is just five years old. The TPC designed it to supersede TPC-C. Microsoft was one of many companies that contributed to the development of TPC-E. We were also an early adopter of TPC-E after recognizing that TPC-C was no longer driving our engineering work in directions that benefited our customers. TPC-E has proven to be a well-constructed workload that has driven many improvements in SQL Server, surfacing performance and scalability issues that had gone undiscovered in more than a decade of running TPC-C.

But don’t take our word for it. This IBM whitepaper [2] explains how TPC-E was designed to be more realistic than TPC-C. There are numerous ways, detailed in the whitepaper, in which TPC-E is far superior to TPC-C. The table below compares TPC-C and TPC-E on several dimensions. As you can see, in TPC-E the schema is substantially richer and more complex, there are twice as many transactions as TPC-C, and only TPC-E requires essential capabilities such as referential integrity and RAID protected storage. Which one looks more like your own databases?

 

TPC-C

TPC-E

Schema

   

Number of database tables

9

33

Foreign keys

9

50

Tables with foreign keys

7

27

Check constraints

0

22

Partitioning Characteristic

unrealistic; single dimension common

to 8 of 9 tables

realistic;

two independent dimensions

Data content

unrealistic; generated random characters

realistic; generated from real data using weighted distributions

Transactions

   

Number of transactions

5

10

Database roundtrips per transaction

1

min 1; max 5

DML statements (approximate)

30

156

Capabilities

   

Referential Integrity Required

No

Yes

Storage Protection (e.g. RAID) for Database Required

Log Only

Everything

Timed Database Recovery test

No

Yes

As of this writing, there are 54 official TPC-E results spanning various hardware platforms and system configurations. What’s surprising is that SQL Server is the only database with official results (i.e., audited, fully disclosed, and open to full review). Although Oracle and IBM are TPC members and contributed to the development of TPC-E, they’ve not shown their database performance running TPC-E. What started as SQL Server being an early adopter of TPC-E has turned into a five year wait for someone to challenge our results.

Perhaps part of the problem is that we’ve not been standing still. As you can see in the graph below, SQL Server performance on TPC-E has increased 7x since our first result in 2007. Of course, hardware has gotten faster and that is part of the story. But we’ve also done lots of work to improve performance and scalability, producing several patents along the way. In fact, without improvements that went into SQL Server 2008, 2008 R2, and 2012, our top performance would be less than half of what it is today.

image

Would we have made the same gains with TPC-C? No. TPC-C is simply too narrow. It exercises a limited set of operations in the database engine. It’s a bit like the difference between drag racing and Formula One. Drag racers are built for extreme acceleration down a straight track. In less than 10 seconds and a quarter mile the race is over. In Formula One racing, though, it’s not enough to just be fast. You need to be able to race for hundreds of miles through twists and turns at times just inches away from other cars going 200 miles per hour. The engineering is much broader and more demanding. Many innovations in performance, efficiency, and safety that start on the F1 race track make their way into the regular cars that we all use every day. The same can’t be said of drag racing.

Years ago, before switching to TPC-E, we prototyped changes to SQL Server that would have improved our TPC-C performance by about 15%. The problem was that it was niche engineering that would have been of minimal value beyond TPC-C. After much debate, we decided against productizing the prototype, even though Oracle and DB2 had already added similar niche optimizations. As much as we would have liked to win the TPC-C drag race, we focused instead on improving performance in areas with the greatest customer benefit. The result is a database with great performance and scalability for wherever the road takes you.


References:

[1] Transaction Processing Performance Council. TPC Benchmark™ E. http://www.tpc.org/tpce/

[2] Tricia Hogan. February 2007. Overview of TPC Benchmark E: The Next Generation of OLTP Benchmarks. IBM white paper. ftp://ftp.software.ibm.com/eserver/benchmarks/wp_TPC-E_Benchmark_022307.pdf