Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I have read that hyperthreading is a "performance killer" when it comes to DBs. However, what I read didn't state which CPUs. Further, it mostly indicated that I/O was "cut to < 10% performance".

That logically doesn't make sense since I/O is primarily a function of controllers and disks, not CPUs. But then no one ever said bugs made sense.

What I read also stated that SQL Server could put two parallel query ops onto 1 logical core (2 threads), thereby degrading performance. I have a hard time believing SQL Server's architects would have made such an obvious miscalculation.

Does anyone have and data on how hyperthreading on current generation CPUs affects either of the RDBMSs I mentioned?

share|improve this question
    
    
I think the answer for this is "Maybe, but to know for sure you need to test your specific workload", which has been the answer whenever Hyperthreading and DBs has popped up here in the past... Hence the link to the other question. –  RobM Jan 7 '11 at 11:37
1  
Afaik the reason why HT was discouraged on pre-Nehalem units was L2 cache sharing between the two hyper-threads in each core. See blogs.msdn.com/b/slavao/archive/2005/11/12/492119.aspx. Nehalem CPUS behave much better. The issue described by ozamora looks more like a specific hardware configuration issue where HT contributed just by adding more CPUs, not an issue with HT itself. In other words, the same issue would had happen if the number of cores would had double and HT would be disabled, so it is not an HT issue. –  Remus Rusanu Jan 8 '11 at 20:24
    
@Remus thanks for the comment & link. I can think with cache contention due to suddenly "doubling" the cores. But I/O at 10% sounds like all sorts of hardware conflicts. By pre-Nehalem, I take it they had Core 2 or even P4? –  IanC Jan 9 '11 at 5:30

1 Answer 1

up vote 5 down vote accepted

What I read also stated that SQL Server could put two parallel query ops onto 1 logical core (2 threads), thereby degrading performance. I have a hard time believing SQL Server's architects would have made such an obvious miscalculation.

This is not a SQL Server problem. Hyper-Threading virtual cores look totally identical to real cores for the operating system - heck, even the bios. You can nail a process to a processor, but the scheduler simply has no idea which of the cores of a processor is real and which is hyper threading... especially as both are tehcnically real, just share certain resources. Hyper-Threading was developped by Intel to allow "cheaper dual core processors than real ones" by sharing certain assets between two cores each, but the cost is that the program simply has no knowledg about this.

Newer Intel CPU's are better inths hardware side, so Hypewr-Threading is not assumed a bottleneck for current SQL Server anymore - running on CURRENT intel chips. This because Intel made Hyperthreading better, primarily.

http://www.microsoft.com/whdc/system/sysperf/Perf_tun_srv-R2.mspx is the current 2008 R2 tuning guide.

http://ozamora.com/2010/09/sql-server-2008-r2-and-nehalem-processors/ has some stuff with Nehalem and SQL Server.

share|improve this answer
    
TomTom thank you for your answer & links. The second one is the one I was referring to re. I/O issues with hyperthreading enabled. –  IanC Jan 7 '11 at 12:10
    
@TomTom On a related note, I would have thought one could simply say, "When on a hyperthreaded CPU, consider every odd numbered CPU to be a 'real' core, and every even numbered one to be a 'virtual' core." This seems logical to me since they are grouped in pairs. Or am I mistaken? –  IanC Jan 7 '11 at 12:10
    
You are. They are in pairs, but who says that this is how the board exposes them? The bios sees them? Implementation details - not documented behavior - makes a crappy base for decisions for enterprise software. –  TomTom Jan 7 '11 at 12:42
    
I guess only Intel could answer that one :) It may be as I say, or it may vary, or it may be any other way. But as you basically say, one cannot design based on assumption. –  IanC Jan 7 '11 at 14:09
1  
@TomTom: actually the OS knows exactly which are HT and which are real threads, and the information is also available to applications: msdn.microsoft.com/en-us/library/ms683194. Some OS scheduler (even as far as XP) actually favors real hardware threads and only start scheduling the HT ones when the real ones are at 100%. See blogs.msdn.com/b/oldnewthing/archive/2004/09/13/228780.aspx –  Remus Rusanu Jan 9 '11 at 5:40

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.