THE SQL Server Blog Spot on the Web

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

Kevin Kline

Startup Switches used in the Recent TPC-H Benchmark

Some people take a rather cynical approach to benchmarking by considering them to be inaccurate representations of what the database platform and hardware can do.  They’re cynical because the benchmarkers take every opportunity to give their system every advantage they can.  They view it as a sort of cheating since most real-world applications never use the techniques employed to gain high scores on a TPC benchmark.  I, on the other hand, don’t look at it so pessimistically.  After all, it’s only cheating if a vendor uses these unique and obscure techniques if they were the only one doing it.  But quite to the contrary, every database platform that has ever undertaken a TPC test has used these exact same techniques.  Since everyone is doing it and everyone is fully documenting the shortcuts they take, it can’t possibly be construed as cheating.

 

Consider raw partitions.  When I used them back in my Oracle days (well over 15 years ago now), raw partitions provided a 5-8% performance boost at most at a high cost of administration and maintainability.  I would never want to administrate a database deployed on raw partitions (although I did have to support a couple).  Any way, raw partitions aren’t used much in commercial IT these days, but the fact that both Oracle and SQL Server and DB2 can and do use them for their TPC benchmarks puts all of them on an equal footing and makes the competition for highest score completely fair.

 

So consider the unique SQL Server startup parameters used by Microsoft and HP for their TPC-H benchmarking run: -c -x - E –T834 –T2301 -T661.  Hmmm… They’re not immediately familiar.  What do they do?

 

§  -x, Disables keeping of CPU time and cache-hit ratio statistics.

§  -c, Starts SQL Server independently of Windows NT Service Control Manager

§  -E, Increases the number of consecutive extents allocated per file to 4

§  -T2301, Enables more accurate query run-time behavior modeling in the SQL Server query optimizer usually only needed for large data set decision support processing.

§  -T834, Causes the buffer pool to use large pages on 64-bit installations of SQL Servers with 8GB or more of memory. These are allocated at startup and are kept throughout the lifetime of the process.

§  -T661 Turn off the ghost thread

 

So now I want to know more.  For example, what is the ghost thread referenced for switch –T6661?

Interesting stuff, eh? What other questions does this bring up with you?

Cheers,

-Kevin

Published Wednesday, June 20, 2007 5:53 PM by KKline
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

 

ACALVETT said:

Very interesting stuff!

I wonder if all 64 bit systems with > 8gb memory would benefit from the 834 flag.

I suspect the ghost thread relates to the ghost cleanup system task. When a record is deleted a bit gets flipped indicating its now a "ghost". The ghost cleanup thread then comes along and deletes it.

June 21, 2007 6:15 AM
 

KKline said:

Excellent comment!  Thanks for the input on the ghost cleanup system task.  By making the cleanup of any deleted records a very low priority (or no priority at all), you can certainly squeeze out bit of extra performance out of the benchmark.

Very cool...

-Kevin

June 21, 2007 5:51 PM
 

WesleyB said:

I think -T834 is no longer needed in RTM version.  I see some of our servers which give messages about large page allocations in the log.

You have an excellent point though when you say that a couple of the settings are not really realistic.  

Great reading material!

June 22, 2007 2:43 AM
 

Scott said:

Have you tried using these switches. Then running SQLSQLIOSIM to see what performance improvement you get?

June 28, 2007 4:40 PM
 

KKline said:

It's my understanding that SQLIOSIM simulates SQL Server behavior as a sort of modeling simulation, but it's not true benchmarking.

July 13, 2007 1:12 PM
 

Jisha said:

Hi Kevin,

Any idea how to simulate a MS SQL Server TPCH(DSS) workload with SQLIO ? what are the parameters to use ?

May 28, 2009 7:36 AM
 

JJ said:

Hi Kevin,

Any idea how to simulate a MS SQL Server TPCH(DSS) workload with SQLIO ? what are the parameters to use ?

May 28, 2009 7:36 AM
 

KKline said:

Unfortunately, I don't have any idea how SQLIO might simulate a TPC-H workload.  I'll start looking into it though.

Thanks,

-Kev

June 30, 2009 11:18 AM

Leave a Comment

(required) 
(required) 
Submit

About KKline

Kevin Kline is a well-known database industry expert, author, and speaker. Kevin is a long-time Microsoft MVP and was one of the founders of PASS, www.sqlpass.org.

This Blog

Syndication

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