THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Joe Chang

TPC-E and TPC-C Comments

As people are well aware, Microsoft is steering away from TPC-C to the new TPC-E for OLTP benchmarking. There is a slide deck on the www.tpc.org website outlining all the reasons for going to a new benchmark over TPC-C which has a very long history and very many results from systems going back to Pentium days.

The following points not discussed elsewhere may provide helpful insight between the two benchmarks. The TPC-C benchmark is comprised of 5 stored procedure calls: New-Order, Payment, Delivery, Order-Status and Stock-Level. The score, in transactions per minute, is the rate of New-Order calls. TPC-C requires a specific distribution of calls, so on average, there are 2.22 calls per New-Order. The new TPC-E benchmark is -comprised of 10 categories. Each category roughly corresponds to a screen or web page on the client side, and like modern information-rich applications, may be comprised of multiple frames or stored procedure calls. If I am interpreting this correctly, then there is an average of 25.4 stored procedure calls for each TPC-E transaction.

Consider the recent TPC-C result of 407,079 tpm-C for the HP ProLiant ML570G5 with 4 quad-core Xeon X7350 2.93GHz processors. This works out to 6,785 transactions per second or 15,062 SQL Batches/sec based on 2.22 calls per transaction, which in turn means the average cost per call is about 1 CPU-millisec. The TPC-E result 479.51 tps-E on the IBM x3850, also with 4 X7350 processors, corresponds to 12,180 SQL Batches/sec, meaning each call averages about 1.2 CPU-ms.

The reason I pay attention to SQL Batches/sec and average CPU per call is this affects performance analysis. A low call rate on a fully loaded server means that average CPU per call is very expensive. It is likely there are expensive operations in the execution plan or CPU is squandered on compiles and recompiles. A very high SQL Batch rate means that there might not be a lot to be gained in query/index tuning and more to be gained in network round-trip reduction. This does not apply to the benchmark because it might be required by the rules.

There are significant differences between TPC-C and TPC-E. The two most common calls in TPC-C are New-Order and Payment. New-Order is comprised of about 11 single row selects, updates, and inserts, assuming the average new order is 10 line items. Payment is comprised 3 single row updates and 1 insert. The bulk of the cost of TPC is in insert and update statements. There is only one join query in TPC-C. TPC-E is comprised of much more select queries with multi-table joins and other common operations.

Published Friday, March 07, 2008 4:47 PM by jchang
Filed under:

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Linchi Shea said:

Benchmarking is always a touchy issue. Like polictics, you really have to read between the lines, and often, silence says a lot more than published results.

March 11, 2008 12:44 AM
 

ted said:

Hi Joe - just ran into your blog, very interesting.

Regarding "The reason I pay attention to SQL Batches/sec and average CPU per call is this affects performance analysis" - how do you tell the average cpu per call?

Thanks,

ted

March 11, 2008 7:14 AM
 

jchang said:

Average CPU-sec / call

= Processor Utilization * number of processors / SQL Batches/sec

it seems too many of us look for the counter instead calculating the value based on other counters

I don't have a serious issue with TPC-E except for the low number of published results. I do think that it accomplished it mission of reducing the required number of disks. TPC-C configs were typically RAID-0, TPC-E is typically RAID-10. The number of disks is about half of C, so the real disk load reduction is probably 4X

March 11, 2008 10:23 AM

Leave a Comment

(required) 
(required) 
Submit

About jchang

Reverse engineering the SQL Server Cost Based Optimizer (Query Optimizer), NUMA System Architecture, performance tools developer - SQL ExecStats, mucking with the data distribution statistics histogram - decoding STATS_STREAM, Parallel Execution plans, microprocessors, SSD, HDD, SAN, storage performance, performance modeling and prediction, database architecture, SQL Server engine

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement