3

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?

IamIC
  • 155
  • 1
  • 7
  • possible duplicate of [Will disabling hyperthreading improve performance on our SQL Server install](http://serverfault.com/questions/194377/will-disabling-hyperthreading-improve-performance-on-our-sql-server-install) – Rob Moir Jan 07 '11 at 11:36
  • 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. – Rob Moir Jan 07 '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 http://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 08 '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? – IamIC Jan 09 '11 at 05:30

1 Answers1

5

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.

TomTom
  • 50,857
  • 7
  • 52
  • 134
  • TomTom thank you for your answer & links. The second one is the one I was referring to re. I/O issues with hyperthreading enabled. – IamIC Jan 07 '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? – IamIC Jan 07 '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 07 '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. – IamIC Jan 07 '11 at 14:09
  • 3
    @TomTom: actually the OS knows exactly which are HT and which are real threads, and the information is also available to applications: http://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 http://blogs.msdn.com/b/oldnewthing/archive/2004/09/13/228780.aspx – Remus Rusanu Jan 09 '11 at 05:40
  • @TomTom: or, to be correct, the OS knows when it had scheduled one execution thread on a core vs. when it had scheduled two (thus triggering HT execution). – Remus Rusanu Jan 09 '11 at 06:03