The TPC Benchmarks

You should make a special effort to understand the TPC benchmark data that the important database vendors now publish. Microsoft published a number of benchmarks shortly after the release of the product indicating that SQL Server 2005 maintains the lead set by its predecessor SQL Server 2000 in DBMS systems with one of the lowest cost per transaction ratings on the market. What does this mean to you?

TPC benchmarks are to the DBMS what the MIPS ratings are to processors or CPUs or what horsepower is to a combustion engine. A DBMS needs to meet certain performance benchmarks before it can be considered worthwhile or competitive in both online transaction processing and decision support systems. The benchmarking is used to test algorithms, new versions of code, new hardware platforms, and so on. So DBMS vendors, including Microsoft, Informix, IBM, Oracle, Sybase, and others, use the benchmarks to test the veracity and reliability of their systems on certain platforms and in controlled testing environments. TPC benchmark tests are like Formula 1 events in motor racing. The manufacturers not only compete to prove superiority or market share, but they take what they learn at the races and incorporate the results into new products for consumers.

The nonprofit TPC-which can be found on the Internet at www.tpc.org-conducts the TPC benchmark tests. The TPC’s purpose is to define the tests and disseminate objective performance data based on the benchmarks. The benchmarks have stringent test requirements and include a number of durability and reliability tests. The results of the tests are independently audited and published.

The two important benchmarks for SQL Server 2005 (and SQL Server version 2000) are the TPC-C benchmarks and the TPC-H benchmarks, respectively, for online transaction processing (OLTP) and decision support systems. They are described as follows:

• TPC-C: OLTP   TPC-C measures both performance and scalability of OLTP DBMS systems. TPC-C tests a wide range of database functionality such as selects, updates, and batch transactions. Most important, however, is that TPC-C measures throughput in business transactions per minute in a simulated order entry and distribution environment. The tests measure how many new orders are generated in a minute while the DBMS is executing four other transaction types, such as payments, status updates, shipping or deliveries, and inventory level updates. Throughput, in TPC terms, is a measure of maximum sustained system performance. The five transaction types have a certain predefined user response time requirement. The new order response time is set at five seconds. So, if your system generates a 500 tpmC number, it means that the system is generating 500 new orders per minute while fulfilling the other four requirements in the TPC-C workload. When you consider Internet applications or e-commerce solutions, you can see how the TPC-C benchmark becomes especially important.

• TPC-H: DSS   This benchmark, measured as TPC-H Composite Queries per Hour (QphH), simulates a decision support system containing large volumes of data that is synchronized with online production databases. The benchmark makes use of highly complex ad hoc queries designed to answer real-world questions. These include questions concerning pricing, promotions (such as target markets), supply and demand, profit and loss, and so on. It should come as no surprise, given the preceding tmpC rating, that SQL Server 2005 also leads the field in the TPC-H benchmarks.

TPC-C and TPC-H also produce price/performance numbers, the $/tpmC and $/QphH rates, respectively. These ratings are especially useful when determining cost for transaction or analysis performance. But the rating is not about the cost of the platform or computer, because that actually represents only a small part of the costing criteria in a DBMS. The rating takes into consideration the entire computing environment, including terminals, client computers, software, operating systems, the DBMS software, three-year maintenance plans, and so forth.

An example: How do you derive the $/tpmC for an OLTP system? Take the cost of the entire system and divide it by the tpmC benchmark. For example, if we take the cost of the entire system (say published at $5.3 million for a solution) and divide that by the tpmC, or 262, 243, to arrive at the $/tpmC of $20.24. No doubt the highly scalable Windows Server platform (and HP server clusters would fill a small data center) is even more juicy than the actual tpmC number because an IBM RS/6000 with about 24 processors would cost you four times the $/tpmC for nowhere near a comparable tpmC.

It is also important to stress an interesting finding about scalability-and we will get into this further in Chapter 9-that the TPC benchmarks prove. When it comes to SMP scalability, less often means more, especially for DBMS systems. Linear SMP scalability is a jihad among DBAs, and many seem to think that midrange or UNIX systems with OS support for hundreds of processors is the answer to achieving DBMS scalability. Many also point out a so-called flaw in Windows Server 2003 that it still cannot support as many processors as its rival UNIX. The benchmarks thus prove SMP scalability to be a myth with respect to DBMS systems.

Note 

The TPC monitors other benchmarks as well, such as the TPC-R rating, which measures the performance of standardized report generation. The benchmarks can be researched at www.tpc.org.