TPC-C: Full Disclosure

The Transaction Processing Performance Council's (TPC's) TPC-C benchmark is the most common way of comparing performance in the database space—and a favorite tool in vendor marketing wars. You might find publicized TPC-C scores valuable in evaluating and enhancing your database environment, but most people ignore another information source associated with TPC-C scores: the full disclosure reports. Independent third parties fully audit all TPC-C reports, writing voluminous reports detailing everything associated with the benchmark. Hardware configuration, database settings, drives for the SAN, costs down to the penny—it's all in there.

The current top SQL Server TPC-C score's full disclosure report is 294 pages long. Most of those pages are filled with mundane information (unless dissecting TPC-C scores is your definition of a great Saturday night!). However, you can glean a few nuggets of valuable information. I spend a lot of time helping my customers tune their SQL Servers, and I love using the full disclosure reports to show customers how Microsoft puts together its top systems. The reports help when a customer wants to argue the finer points of my tuning solutions. Some customers don't like to rely on my word alone but are satisfied when I show them "how Microsoft does it."

When sharing TPC-C full disclosure information with my customers, I refer most to the configuration setting for max degree of parallelism (MAXDOP). Many customers are surprised that almost every published Microsoft TPC-C score has the MAXDOP set equal to 1. This settingmeans that SQL Server won't use a parallel execution plan for any query. You might ask, "Aren't parallel queries faster than a serial counterpart for an execution plan?" The answer to that question, of course, is, "It depends." The TPC-C benchmark measures performance for an online transaction processing (OLTP) workload, and most OLTP workloads don't benefit from parallel queries. For example, if a particular expensive parallel plan decides to chew up all eight processors in the middle of a peak transaction-processing time, your overall throughput can dramatically drop. I usually recommend that my customers set the MAXDOP value equal to 1 (disabling parallelism) for most OLTP workloads. I recommend you do the same unless you've performed serious in-depth testing to prove that keeping parallelism enabled is the right choice for your environment. Even then, your testing becomes obsolete and meaningless if you introduce new queries, which can change your well-thought-out plans. It's better to disable parallelism for OLTP workloads.

I can't summarize a 294-page report in this space, but I think you'll find it worth your time to sit down with a full disclosure report for about an hour. You're bound to pick up some interesting and useful tidbits of tuning information. Think through the settings Microsoft chooses. TPC-C workloads might not mirror your environment 100 percent, but you can be assured that Microsoft spends considerable time making sure its settings are as fast as possible.