SQL Server Performance Forum – Threads Archive

Dual Core and Hyperthreading

Hi All, My question is this: On a 4way box (DELL6850) as it happens.
With 2 Dual-Core processors. Q:
Is it advisable to also turn on Hyperthreading for the cores?
Or better to not use Hyperthreading?
IME SQL2000 sometimes had some problems with parallell plans,
hopefully this is an area where SQL2005 is more robust. This is running SQL2005, high transaction volume database.
Regards,
rockmoose

From my experience, SQL Server 2000 doesn’t do well with hyperthreading. I am told that SQL Server 2005 knows about hyperthreading and can deal with it better, although I have no personal experience with this combination yet. —————————–
Brad M. McGehee, SQL Server MVP

From one of the Technet [email protected]

quote:
Q What are the advantages and disadvantages of using multicore processors and hyperthreading processors with SQL Server 2000 and SQL Server 2005? A This question comes up quite frequently. The general rule for multicore systems is that they provide a benefit to performance without your having to do anything to get the benefit. The story isn#%92t so clear for hyperthreading. Some folks will tell you that for zero administration reasons you should disable this. More experienced administrators should test with their workload to determine if hyperthreading might provide a benefit. Here the operating system should also be considered. Windows 2000 Server is less hyperthreading aware than is Windows Server 2003. Windows Server 2003 SP1 has further hyperthreading enhancements, providing the best results with this OS.

Also Slava’shttp://blogs.msdn.com/slavao/archive/2005/11/12/492119.aspx blog in this case, fyi. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing.

Thanks for the input. I still have not been able to make a decision on the matter.
But i am leaning towards off, and that is mostly due to the fact that we also experienced some problems with sql2000. So this reason might be more emotional than based on the factual current state of affairs on sql2005.
rockmoose

Can you simulate this application load on test environemnt? Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing.

> "Can you simulate this application load on test environemnt?" Hi, no I can’t…

Then i would suggest to take help of Microsoft support in this case if you have such contract. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing.

i suggest one of the following 1. enable HT, but only allow SQL to use the lower 4 procs, which should be 1 logical per core. 2. disable HT. 1. is preferred if you use Quest LiteSpeed for backups and you have very good disk system performance
HT is to problematic and only helps in 1 know specific condition

Thank you. My plan is to disable the HT,
CPU has never been our bottleneck, always RAM and disk IO.
We have trebled the RAM and moved to SQL2005, and the system has not been load-tested under this new environment yet. I am going to have a discussion with the techs at our hosting company who set up the original HT.
And hear what arguments and insights they might have about this. rockmoose

Make sure to input those arguments here to for further information and knowledge sharing. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing.

quote:Originally posted by satya Make sure to input those arguments here to for further information and knowledge sharing. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing.

There is not much to share.
They were happy to comply with my request to disable hyperthreading,
and had no arguments against it.
I asked if there was any special reason why they used ht, and the answer was that
theoretically it provided better performance. But they had also had other clients
disable/enable ht without any noticeable effects on performance. rockmoose

[<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] much expected as always, thanks for the feedback. <hr noshade size="1">Satya SKJ Microsoft SQL Server MVP Contributing Editor & Forums Moderator http://www.SQL-Server-Performance.Com</a> <center>This posting is provided AS IS with no rights for the sake of knowledge sharing.</center>

so what it comes down to is they have no direct evidence as to when HT improves performance, and for that matter, when it has negative effects see my material below for when it helps
http://www.sql-server-performance.com/joe_chang.asp i never got around to publishing this, but SQL Server can have problems on disk IO when using all logical procs, HT enabled
but it is also possible this has a resolution

]]>