SQL Server Performance

Dual Core and Hyperthreading

Discussion in 'SQL Server 2005 Performance Tuning for Hardware' started by rockmoose, Dec 5, 2006.

  1. rockmoose New Member

    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
  2. bradmcgehee New Member

    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
  3. satya Moderator

    From one of the Technet articles@

    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.
  4. rockmoose New Member

    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

  5. satya Moderator

    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.
  6. rockmoose New Member

    > "Can you simulate this application load on test environemnt?"

    Hi, no I can't...
  7. satya Moderator

    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.
  8. joechang New Member

    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
  9. rockmoose New Member

    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
  10. satya Moderator

    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.
  11. rockmoose New Member

    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

  12. satya Moderator

    [<img src='/community/emoticons/emotion-1.gif' alt=':)' />] much expected as always, thanks for the feedback.<br /><br /><hr noshade size="1"><b>Satya SKJ</b><br />Microsoft SQL Server MVP<br />Contributing Editor & Forums Moderator<br /<a target="_blank" href=http://www.SQL-Server-Performance.Com>http://www.SQL-Server-Performance.Com</a><br /><center><font color="teal"><font size="1">This posting is provided AS IS with no rights for the sake of <i>knowledge sharing.</i></font id="size1"></font id="teal"></center>
  13. joechang New Member

    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

Share This Page