There's been alot of negative publicity about hyperthreading (see "Hyperthreading hurts server performance, say developers" which is all over the net...including: http://news.zdnet.co.uk/0,39020330,39237341,00.htm)
We had been experiencing disapointing results upgrading from a 32 bit dual processor/single core machine to a 64-bit quad processors/dual core machine with hyperthreading...so I figured this was a good setting to experiment with.
The other setting that gets alot of attention is the "Max Degree of Parallelism--Max DOP". This setting tells SQL Server how many processors to use when running a multiple query. If set to 0, it uses all available processors. Otherwise you can limit it to a lower # using this paramater. The advice on the internet ranges from "leave it to 0" (which means use all processors), to "set it to the # of processors but don't count your hyperthreaded processors as 2" to "set it to 1". So this was also a good setting to experiment with.
Environment:
So I performed several tests using the http://www.RentACoder.com database to determine if it would help. I picked a period of the night when the traffic was low (in case something got really messed up) but relatively constant and no odd batch jobs or mailing lists were running.
Equipment: Quad processor (3.1 ghz) Dual Core 64-bit machine with 16GB memory.
Software: SQL Server 2005 64 bit edition and Windows 2003 SP1.
(note: some articles said that Windows 2003 SP1 was the most optimized system for hyperthreading).
Raw Test results:
1)Baseline: Hyperthreading on...MAXDOP=0: At 9PM (a lull period) with hyperthreading enabled, the CPU usage ranged from 50-60%. The MAXDOP=0 used 16 virtual processors (4 physical x 2 cores/processor x 2 hyperthreads/processor)
2) Hyperthreading off...MAXDOP=0. The CPU usage increased a small but noticable amount...and ranged from 60-70%. The MAXDOP=0 used 8 virtual processors (4 physical x 2 cores/processor)
3) Hyperthreading off...MAXDOP=4. This test tested setting it to the # of physical processors (4). This was a CPU killer. Usage spiked to 90-100%.
Conclusion:
In this case, hyperthreading really did produce a measureable boost in performance of 10%. Deactivating it permanently, would have dropped performance unnecessarily.
Setting MAXDOP = to the # of physical processors really killed performance...the default MAXDOP setting=0 performed much better. Note: I didn't try changing MAXDOP with hyperthreading enabled...perhaps that is a test for another time.
Bottom Line:
Don't trust "experts" who tell you that hyperthreading doesn't work...test it for yourself. You may get a boost as we do from it. MAXDOP tuning did not produce an increase in performance and actually hurt performance. So if you experiment do so during a lull period where you won't flood yourself with complaints if your CPU usage spikes.
Ian Ippolito