To Hyperthread or Not To Hyperthread

Following on from my comments about CPU configuration I have decided to restate my position to "it depends....". 

Having performed some investigation in the Sql community, Glenn Berry ( Twitter | Blog )  suggested that Hyper Threading should be enabled for the new Xeon 55xx, 56xx, 65xx, and 75xx (Nehalem)  processors and pointed me in the direction of the TPC-E benchmarks for evidence of their performance.

According to the benchmarks in this article http://www.qdpma.com/Benchmarks_TPCH.html  OLTP systems (TPC-E) benefit from a 30% performance gain from hyperthreading while Data Warehousing  (TPC-H) benefitted only 10% from hyperthreading.

Still concearned about the potential issues with Hyper-threading I descided to investigate further. I found this great article by Joe Chang suggesting that many of the problems with the original Hyperthreaded Netburst processors have gone away with the advent of Nehalem. He just suggested setting MAXDOP to 4 as a general rule.

Given that there is such a great performance gain to be had for Hyperthreading with OLTP, I do intend to turn it on for servers containing the new Nehalem chipset. For DW servers, I will still leave it disabled as I have spent many an hour tracking down various issues with CXPACKET and Intra Query Parallel Thread Deadlocks. I still dont trust the technology enough to go there at this point.

@Blakmk

 

 

Published 04 August 2010 20:13 by blakmk
Filed under: , ,

Comments

04 August 2010 20:19 by The Tao Of Sql Server

# Sql Server Consolidation and Configuration Part 1 CPU

So i only seem to get inspired when im totally immersing myself in a project. Having been busy for a

04 August 2010 21:40 by Twitter Trackbacks for To Hyperthread or Not To Hyperthread - The Tao Of Sql Server [sqlblogcasts.com] on Topsy.com

# Twitter Trackbacks for To Hyperthread or Not To Hyperthread - The Tao Of Sql Server [sqlblogcasts.com] on Topsy.com

Pingback from  Twitter Trackbacks for                 To Hyperthread or Not To Hyperthread - The Tao Of Sql Server         [sqlblogcasts.com]        on Topsy.com

06 August 2010 00:37 by mastic

# re: To Hyperthread or Not To Hyperthread

I just wanted to point out the quote from Joe Chang concerning the 10% HT benefit from TPC-H was not referenced in his posting and not derived from any data presented.  In fact the actual numbers have trailing question marks indicating Joe himself does not know if they are correct.

I also find it interesting the number of TPC-H publications that enable HT vs. those that don't.  I realize benchmark publications try to tweek every last percentage of performance out of the system and these tweeks might not be representative of real-world scenarious.

However, I would like to see a more conclusive analysis before making a decison on HT vs no HT and appropriate MAXDOP settings.

06 August 2010 12:16 by GrumpyOldDBA

# re: To Hyperthread or Not To Hyperthread

It's more than just a case of turning it on and getting +30%. Adding HT doubles your schedulers, each scheduler takes resource especially on an x64 system so you could find you get serious issues. setting maxdop is to a certain extent application dependant and just doubling your schedulers , which is similar to doubling the cores/cpu count, will only give you gains if your current resource is under pressure. A good many people got burned thinking lots of cores would give lots more performance, it's bit like buying car that has a top speed of 155mph instead of 100mph, if you only ever drive at 50mph you'll not see a difference.

07 August 2010 13:10 by blakmk

# re: To Hyperthread or Not To Hyperthread

mastic, the 10% HT benefit for TPC-H is mentioned in this article: www.qdpma.com/Benchmarks_TPCH.html

I think the maxdop setting is just a guide from him, which im sure could be tweaked either way. As with anything like this the best way is to test on your own environment.

GrumpyOldDBA, I used to think the same however it appears the SQLCAT team are recommending turning this on as the original problems with HT are not present in Nehalem. However as with anything it all depends on the environment. My personal take on this is not to use on DW because the performance gains are not compelling.

If either of you have any recent experiences with Nehalem I would be interested to hear.

01 September 2010 17:34 by SQL Server 2008 R2 and Nehalem Processors | Zamo's Blog

# SQL Server 2008 R2 and Nehalem Processors | Zamo's Blog

Pingback from  SQL Server 2008 R2 and Nehalem Processors | Zamo's Blog