SQL Server Performance

Windows 2000 Server with SP4 + 4 CPUs with HTT

Discussion in 'Performance Tuning for Hardware Configurations' started by chopeen, Sep 3, 2004.

Page 1 of 2

  1. chopeen Member

    Probably you are fed up with this questions, but I really need you help, guys, since Dell support people and Microsoft documentation are giving me inconsistent information.

    Consider a server with 4 Xeons (HTT enabled) running on top of Windows 2000 Server with latest SP installed.

    How many logical CPUs will the system use? 4 or 8?
    How many logical CPUs will SQL Server use? 4 or 8?
    chopeen, Sep 3, 2004
    #2

  2. satya Moderator

    satya, Sep 3, 2004
    #2

  3. chopeen Member

    quote:Originally posted by satya

    8, as we have on one of our services.

    8 CPUs will be used by the OS and by SQL Server, right?
    chopeen, Sep 3, 2004
    #2

  4. satya Moderator

    satya, Sep 3, 2004
    #2

  5. chopeen Member

    OK. Thanks a lot.
    chopeen, Sep 3, 2004
    #2

  6. joechang New Member

    Win 2000 Server Standard or Enterprise Edition,
    std should see only 4
    EE should see 8
    joechang, Sep 3, 2004
    #2

  7. derrickleggett New Member

    Here are your choices:

    Windows 2000 Standard/SQL Server 2000 Standard - 4 Processors
    Windows 2000 Standard/SQL Server 2000 Enterprise - 4 Processors
    Windows 2000 Advanced/SQL Server 2000 Standard - 4 Processors
    Windows 2000 Advanced/SQL Server 2000 Enterprise - 8 Processors

    On any configuration that will use less than 8 processors, you need to disable HTT if this is a dedicated SQL Server.

    MeanOldDBA
    derrickleggett@hotmail.com

    When life gives you a lemon, fire the DBA.
    derrickleggett, Sep 3, 2004
    #2

  8. chopeen Member

    quote:Originally posted by joechang

    Win 2000 Server Standard or Enterprise Edition,
    std should see only 4
    EE should see 8

    But do you mean physical CPUs or logical CPUs?
    chopeen, Sep 4, 2004
    #2

  9. derrickleggett New Member

    Logical. 2000 doesn't really differentiate between the two.

    MeanOldDBA
    derrickleggett@hotmail.com

    When life gives you a lemon, fire the DBA.
    derrickleggett, Sep 4, 2004
    #2

  10. chopeen Member

    So I am confused again.<br /><br />I've got Satya and Dell guys on one side ot the barricade and Joe, Derrick and MS docs on the other. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />Anyway, another question -- I have been told that I would not be able to disable HTT on this machine (there is supposedly no such option in BIOS). Can this cause a problem with starting SQL Server?
    chopeen, Sep 4, 2004
    #2

  11. derrickleggett New Member

    ??? Are you serious. You have a server from Dell that can't disable HTT? What model of server is this? I could possibly have some docs on it if you let me know. I have a sizable archive of server specs on hand.

    MeanOldDBA
    derrickleggett@hotmail.com

    When life gives you a lemon, fire the DBA.
    derrickleggett, Sep 4, 2004
    #2

  12. chopeen Member

    quote:Originally posted by derrickleggett

    ??? Are you serious. You have a server from Dell that can't disable HTT? What model of server is this? I could possibly have some docs on it if you let me know. I have a sizable archive of server specs on hand.
    I am surprised, too. But I called them and that is what they told me. I don't know -- maybe it is only a matter of BIOS update.

    I am doing a research on Power Edge 6600 servers, so if you have some docs on it, let me know.
    chopeen, Sep 4, 2004
    #2

  13. satya Moderator

    On the DELL box look in the System BIOS under the CPU Information. It is called Logical Processing.

    You need to do it through the system BIOS...hit F12 (I think... but it says it on the Dell logo screen) on the Dell logo screen, and the option is in there, called Hyperthreading... set it to disabled.

    HTH

    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.
    satya, Sep 6, 2004
    #2

  14. derrickleggett New Member

    I have a spec on that server only. Do you already have that Marek?

    MeanOldDBA
    derrickleggett@hotmail.com

    When life gives you a lemon, fire the DBA.
    derrickleggett, Sep 6, 2004
    #2

  15. chopeen Member

    quote:Originally posted by derrickleggett

    I have a spec on that server only. Do you already have that Marek?

    No.

    If you could send it to me (e-mail address removed by chopeen) or upload it somewhere, I'd be grateful.
    chopeen, Sep 7, 2004
    #2

  16. satya Moderator

    Marek

    Have you treid to disable HTT on Dell box, as defined above.

    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.
    satya, Sep 7, 2004
    #2

  17. chopeen Member

    quote:Originally posted by satya

    Have you treid to disable HTT on Dell box, as defined above.

    The server still exists only as a design. But I hope that I will be able to check your advice in a few days.
    chopeen, Sep 7, 2004
    #2

  18. aawara New Member

    Going back to the orignal question about HT, if you are setting up a query intensive server, you have to be very carefull. SQL does not diffrentiate between a logical and physical unit. If you have enabled parralel processing, the shadows unit could slow down the response time considerably. YOu may want to do testing and perhaps use some switch (MAX DOP) in your query.
    aawara, Oct 15, 2004
    #2

  19. chopeen Member

    quote:Originally posted by aawara

    SQL does not diffrentiate between a logical and physical unit. If you have enabled parralel processing, the shadows unit could slow down the response time considerably.
    Do you know any articles / whitepapers, preferably Microsoft's or Intel's, that confirm this?

    Because I'm gonna need something official to convince my managers.
    chopeen, Oct 17, 2004
    #2

  20. satya Moderator

    satya, Oct 18, 2004
    #2

  21. chopeen Member

    quote:Originally posted by satya

    Have you treid to disable HTT on Dell box, as defined above.
    I finally had a chance to check your advice. You were right.

    In 'CPU Information' section of BIOS there is option 'Logical Processor' that can be set to either 'Enable' or 'Disable'.

    --

    'You're so cute when you're frustrated.' -- Interpol
    chopeen, Oct 19, 2004
    #2

  22. satya Moderator

    satya, Oct 19, 2004
    #2

  23. chopeen Member

    quote:Originally posted by satya

    So what was the action enable or disable?
    It is a brand new machine, so it was set to enable.

    Last night I run a few very resource intensive jobs on this server. Tonight I will run them one more time with HTT disabled and I'll post the results tomorrow.

    --

    'You're so cute when you're frustrated.' -- Interpol
    chopeen, Oct 19, 2004
    #2

  24. chopeen Member

    I run the same jobs twice in similar conditions, only changing the HTT setting in BIOS. The jobs are mainly responsible for loading some very large reporting tables (very complex SELECTs + large INSERTs).

    The performance of SQL Server 2000 was slightly better (5-10%) with HHT disabled than with HTT enabled.

    --

    'You're so cute when you're frustrated.' -- Interpol
    chopeen, Oct 20, 2004
    #2

  25. chopeen Member

    A very old topic, but I've found an interesting article:

    http://news.zdnet.co.uk/hardware/0,39020351,39237341,00.htm

    --

    "Recommended By Dr. Audioscrobbler."
    http://www.last.fm/user/chopeen/
    chopeen, Dec 1, 2005
    #2

  26. bertcord New Member

    bertcord, Dec 4, 2005
    #2

  27. nkvernum New Member

    Just some interesting reading regarding performance degradation and HT.

    http://news.zdnet.co.uk/0,39020330,39237341,00.htm


    "Our customers observed very interesting behaviour on high-end HT-enabled hardware. They noticed that in some cases when high load is applied SQL Server CPU usage increases significantly but SQL Server performance degrades," wrote Ocks.



    Ocks then detailed testing which showed this behaviour where a system thread — in this case one cleaning out blocks of disk cache memory — is running at the same time as worker threads. "With Intel HT technology, logical processors share L1 & L2 caches. As you would guess [this] behaviour can potentially trash L1 & L2 caches," he said.



    The on-chip cache exists to speed operation by keeping copies of recently accessed data where it can be accessed without recourse to main system memory — which is much slower by comparison. Where multiple threads access different parts of memory but are simultaneously processed by the chip's Hyperthreading Technology, the shared cache cannot keep up with their alternate demands and performance falls dramatically, according to analysis by Ocks and Ibbotson.



    "It's ironic," said Ibbotson. "Intel had sold hyperthreading as something that gave performance gains to heavily threaded software. SQL Server is very thread-intensive, but it suffers. In fact, I've never seen performance improvement on server software with hyperthreading enabled. We recommend customers disable it when running Citrix and our software on the same server."
    nkvernum, Dec 5, 2005
    #2
Page 1 of 2

Share This Page