SQL Server Performance

SQL Problems with Hyper-Threading

Discussion in 'Performance Tuning for Hardware Configurations' started by citiphil, Apr 29, 2004.

  1. citiphil New Member

    Hi,
    Has anyone had any problems with SQL 2000 and Hyper-Threading? I've read conflicting reports online that SQL 2000 is capable of using HTT however others say that this configuration is suicide. Any thoughts from anyone who has experience in HTT would be greatly appreciated!

    CitiPhil
  2. gaurav_bindlish New Member

    It ddepends on your particular application. On some of the applications I have seen that enabling HT increased performance and for others, it was an performance killer.

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

    The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.
  3. bradmcgehee New Member

    We use it all the time and have never had any issues.

    -----------------------------
    Brad M. McGehee, MVP
    Webmaster
    SQL-Server-Performance.Com
  4. derrickleggett New Member

    I've never seen any problems with it either. The only time you don't want to use it is if your OS can't recognize all the drives it would produce. SQL Server really takes advantage of the additional parallelism available by hyper-threading. The only thing you will want to do is monitor. Make sure you don't have some query just going crazy with parallelism and causing problems. As long as you do this, I couldn't imagine not having my hyper-threading on (we didn't for a long time).

    Just make sure you are on the latest service packs for everything.

    MeanOldDBA
    derrickleggett@hotmail.com

    When life gives you a lemon, fire the DBA.
  5. satya Moderator

    Hyperthreading is an interesting new technology that has the potential to yield good performance gains on a number of applications. However, it is important to understand that hyperthreading does NOT guarantee the same performance as an existing machine with two processors. The purpose of hyperthreading is to allow the CPU to remain idle less - however, it doesn't guarantee the throughput of the system (disk IO, memory bandwidth, etc). However, that may or may not be your performance issue on your query.

    But as of now no issues at our end too.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  6. Argyle New Member

  7. I'm using 4 way zeon 4's ( 8 procs with HT ) and used 2 way zeon 4's previously ( 4 procs with HT ) never had any problems and got a good boost for performance. Only thing I'd say is that if you have poor sql queries that give paralellism problems HT will make it worse!! Had problems with cxpacket locks with HT that were generated by out of date stats.
  8. derrickleggett New Member

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by colin leversuch-roberts</i><br /><br />I'm using 4 way zeon 4's ( 8 procs with HT ) and used 2 way zeon 4's previously ( 4 procs with HT ) never had any problems and got a good boost for performance. Only thing I'd say is that if you have poor sql queries that give paralellism problems HT will make it worse!! Had problems with cxpacket locks with HT that were generated by out of date stats.<br /><hr height="1" noshade id="quote"></blockquote id="quote"></font id="quote"><br /><br /><img src='/community/emoticons/emotion-1.gif' alt=':)' /> You really, really, really need to listen to this. Your whole performance tuning process needs to change when you make an upgrade from an old system to a multi-processor HT server. The paralellism is one of the interesting things to tune that can have large affects on overall performance. There will be areas you will sacrifice a slightly longer runtime (reports) by setting the MAXDOP down so other things can continue to use the width of your multiprocessors. It's a fun and rewarding change of pace.<br /><br />MeanOldDBA<br />derrickleggett@hotmail.com<br /><br />When life gives you a lemon, fire the DBA.

Share This Page