Be aware: To Hyper or not to Hyper

re: Be aware: To Hyper or not to Hyper

Peter Ibbotson — Mon, 14 Nov 2005 12:45:57 GMT

We've noticed this effect too. Interestingly we've also seen it on Terminal Server/Citrix machines too. Turning off HT made an unusable system suddenly usable.
I suspected the problem was something like this. What implications does this have for dual-proc machines? I'd guess very little as they don't share cache and I presume there aren't multiple system threads spawned (and I'd guess the code is a little smarter over cache coherency).

re: Be aware: To Hyper or not to Hyper

Haidong Ji — Mon, 14 Nov 2005 20:12:12 GMT

Great information Slava. Thanks for sharing.

I've thoroughly enjoyed your blogs so far. Keep up the great work.

re: Be aware: To Hyper or not to Hyper

John Stephens — Mon, 21 Nov 2005 20:25:30 GMT

Can you please explain your results in more detail?
I'm not sure what I'm supposed to be looking for.

re: Be aware: To Hyper or not to Hyper

Scotty — Wed, 23 Nov 2005 19:19:47 GMT

From your explanation I take it that multiple processors or multiple cores should not show the same problem because of the lack of shared L1 and L2 cache?

re: Be aware: To Hyper or not to Hyper

slavao — Wed, 23 Nov 2005 21:05:50 GMT

Q1: What implications does this have for dual-proc machines?
Q2: From your explanation I take it that multiple processors or multiple cores should not show the same problem because of the lack of shared L1 and L2 cache?
A12: That is correct. Since dual procs don't share caches we shouldn't see this behavior. The same stands for cores that share neither L1 nor L2.
Q3: I'm not sure what I'm supposed to be looking for
A3: From the post: ...If the theory is correct a lock thread that shares a physical CPU with a scan thread should acquire lock much less often than other threads in a given period of time... From output above you could see whenever lock thread shares physical CPU with scan thread it acquires lock much less often as compare to other threads.

Err...

audiofreak — Tue, 29 Nov 2005 21:27:14 GMT

Shouldn't you include Sleep(0) after SetThreadAffinityMask() before doing any work if you want to be sure that the thread runs on the core you requested?
Also, shouldn't pause instruction be inserted immediately BEFORE memory access instruction used to check the lock for it to have any effect?

re: Be aware: To Hyper or not to Hyper

slavao — Tue, 29 Nov 2005 22:19:28 GMT

Q1:Shouldn't you include Sleep(0) after SetThreadAffinityMask() before doing any work if you want to be sure that the thread runs on the core you requested?
A1:Nope, you don't have to. If a call to the API succeeds the thread will be bound to the CPUs described by the mask. For more info see MSDN article http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dllproc/base/setthreadaffinitymask.asp.
Q2:Also, shouldn't pause instruction be inserted immediately BEFORE memory access instruction used to check the lock for it to have any effect?
A2:No, you actually don't have to. http://www.devx.com/assets/intel/9315.pdf has a good explanation for the pause

re: Be aware: To Hyper or not to Hyper

guzmanda — Sun, 04 Dec 2005 18:17:29 GMT

Thanks a lot for sharing, Slava.

I ran a controlled application test on a 4-way (8 logical) server with HT disabled/enabled at the BIOS level and observed 15-20% improvement with HT enabled. Perhaps my observation was contrary to your curtomer's experience because our app had an OLTP profile, with a ratio of about 90% read. It may be that the lasywriter didn't have much work to do and there were few scans in my case.

I believe this underscores your statement about the importance of application testing under load in order to understand HT implications.

re: Be aware: To Hyper or not to Hyper

Jon A [UK] — Thu, 08 Dec 2005 18:33:08 GMT

Just ran some testing with 6GB DB that is updated every minute 24/7 and has frequent reporting. One of the more CPU intensive reports runs:

with HT enabled: 13 seconds
with HT disabled (thru BIOS): 7 seconds

Was testing with:
Dell PowerEdge 2850
W2003 Server Std
SQL2005 Std Edition
2 x XEON CPUs
4GB RAM

I'm going to do more experimenting before deciding on disabling on our production server.

re: Be aware: To Hyper or not to Hyper

Jon A [UK] — Thu, 08 Dec 2005 18:33:35 GMT

re: Be aware: To Hyper or not to Hyper

Jerry Foster — Mon, 12 Dec 2005 17:09:01 GMT

When we switched to 2000 sp4, our system became almost unusable at times. We noticed a drastic increase in CPU usage but performance just tanked, especially under load. I wish Slava could run his test after upgrading to sp4 to see if it gets worse.

We've considered disabling HT, but are afraid to risk making our current problems even worse.

We do have before/after test environments, but unfortunately we simply cannot recreate the necessary load level from our production box to accurately test.

re: Be aware: To Hyper or not to Hyper

Rosa — Wed, 21 Dec 2005 18:40:11 GMT

Does setting the "max degree of parallelism" in SQL Server to the number of physical processors have the same effect as disabling HT? Or is it better to disable HT?

re: Be aware: To Hyper or not to Hyper

jrobertobr — Wed, 04 Jan 2006 15:49:28 GMT

I think reducing max degree of parallelism could cause a query to run only on the logical processors, because all processors are still avaliable to SQL.
I guess disabling the use of logical processors by SQL server (Processor Control under SQL Server Properties), not disbling HTT on BIOS should give the same performance improvements on SQL server, leaving the OS with all the processors.
Again, it's all a case-by-case situation, that must be tested before any action to be taken.

HyperThreading and SQL

Watson's Ramblings — Wed, 22 Feb 2006 00:30:38 GMT

re: Be aware: To Hyper or not to Hyper

Rusty — Wed, 12 Apr 2006 18:12:28 GMT

A well presented article. As a matter of precaution on my SQL servers I disabled HP by default.

Lessons learned from upgrading to SQL Server 2005 and upgrading the hardware

Real Life Microsoft IT — Sat, 29 Apr 2006 00:05:10 GMT

We recently upgraded from SQL Server 2005 (from SQL Server 2000) and also simultaneously the hardware...

Hyper-Threading and Shared CPU Cache

Craig Stuntz's Weblog — Thu, 13 Jul 2006 21:15:21 GMT

ADO.NET and asynchronous commands

Watson's Ramblings — Fri, 01 Sep 2006 18:42:19 GMT

Maximum Degree of Parallelism and Hyperthreading

Lara's Blog — Mon, 13 Nov 2006 19:17:01 GMT

For those who are challenged with deciphering how to configure the max degree of parallelism on a server...

Maximum Degree of Parallelism and Hyperthreading

Lara's Blog — Sun, 19 Nov 2006 07:38:16 GMT

For those who are challenged with deciphering how to configure the max degree of parallelism on a server

re: Be aware: To Hyper or not to Hyper

Glen Sidelnikov — Thu, 07 Dec 2006 17:11:06 GMT

Would this explain an actual performance degradation we are currently seeing on our production box? We are using x64 8 XEON CPU with 16 GB or RAM with WIndows 2003 R2 Enterprise and SQL 2005 64 bit Enterprise. "Production" box is still in testing mode. There is no heavy load. Sproc batch that is running 15 seconds on the "test" box (Two Zeon CPU x86 with 4 GB of RAM running Windows 2003 32 bit + SQL 2005 Standard) is running 24 seconds on production.

Thank you.

re: Be aware: To Hyper or not to Hyper

slavao — Wed, 03 Jan 2007 02:44:15 GMT

Most likely not. In order to find the real culprit you will need to do perf analysis

1. Make hardware sanity check: Disks, CPUs cache sizes, and etc... For example It is better for 64bit systems have larger CPU caches.

2. Check query plan

3. Check perfmon counters and find possible discrepancies that will explain the degradation.

[Performance] Hyper-threading and .NET » Advanced .NET Debugging

[Performance] Hyper-threading and .NET » Advanced .NET Debugging — Fri, 12 Jan 2007 11:49:23 GMT

PingBack from http://dotnetdebug.net/2005/11/20/performance-hyper-threading-and-net/

Yedda: RE: Is Hyperthreading Still being used in new processors?

MrViklund's answers on Yedda - People. Sharing. Knowledge. — Tue, 15 May 2007 20:15:12 GMT

MrViklund answered: re:After Intel launched the new dual core processors, is Hyperthreading still relevant? Is it still being used in new Intel processors?

The Perils of Hyperthreading for SQL Server

Kevin Kline — Mon, 20 Aug 2007 11:02:51 GMT

Just a quick note to send a big Thank You to Christoph Stotz of Frankfurt, Germany for his hospitality

DBS Works » Blog Archive » Programming references

DBS Works » Blog Archive » Programming references — Fri, 19 Oct 2007 19:54:03 GMT

PingBack from http://dbsworks.com/?p=8

Προβληματισμός: To Hyper or not to Hyper

Καθημερινή Ρουτίνα — Mon, 11 Feb 2008 02:36:59 GMT

Ο προβληματισμός γεννήθηκε στην μέση της εγκατάστασης μιας εφαρμογής που έκανε χρήση των Analysis Services

Welcome -- Ax Database Configuration Checklist part 1

Dynamics Ax Performance team — Wed, 12 Mar 2008 21:54:15 GMT

Welcome to the Dynamics Ax Performance Team's blog. We're putting together a team introduction and hope

Ax Database Configuration Checklist part 1 « Cool hakE

Ax Database Configuration Checklist part 1 « Cool hakE — Thu, 24 Jul 2008 03:35:32 GMT

PingBack from http://coolhake.wordpress.com/2008/07/24/ax-database-configuration-checklist-part-1/

Hyper threading e degrado delle prestazioni | hilpers

Hyper threading e degrado delle prestazioni | hilpers — Wed, 21 Jan 2009 19:22:14 GMT

PingBack from http://www.hilpers.it/2532841-hyper-threading-e-degrado-delle

100% du CPU avec base de donn?es. - Page 5 | hilpers

100% du CPU avec base de donn?es. - Page 5 | hilpers — Thu, 22 Jan 2009 18:36:37 GMT

PingBack from http://www.hilpers.fr/918559-100-du-cpu-avec-base/5

Slava Oks s WebLog Be aware To Hyper or not to Hyper | Wood TV Stand

Slava Oks s WebLog Be aware To Hyper or not to Hyper | Wood TV Stand — Mon, 01 Jun 2009 20:40:36 GMT

PingBack from http://woodtvstand.info/story.php?id=11938

Slava Oks s WebLog Be aware To Hyper or not to Hyper | Paid Surveys

Slava Oks s WebLog Be aware To Hyper or not to Hyper | Paid Surveys — Tue, 02 Jun 2009 10:06:36 GMT

PingBack from http://paidsurveyshub.info/story.php?id=76073

Slava Oks s WebLog Be aware To Hyper or not to Hyper | Insomnia Cure

Slava Oks s WebLog Be aware To Hyper or not to Hyper | Insomnia Cure — Wed, 10 Jun 2009 04:04:54 GMT

PingBack from http://insomniacuresite.info/story.php?id=1820

Ax Database Configuration Checklist Part 1

Pin Wang's AX Blog — Tue, 16 Jun 2009 23:58:44 GMT

Assumptions : Dedicated SQL Server 2005 Server (does not run any other major applications besides SQL