SQL 2005 and hyperthreading

Posted on 2007-10-10
Last Modified: 2013-11-29
I have a new x64 2 node cluster 4 - dual core procs 32GB RAM setup by our engineers.  They enabled hyperthreading and the OS is now showing 16 procs.  I plan to install SQL 2005 Ent on this and I'm thinking hyperthreading may not be in our best interest.  I'm concerned with the extra overhead affecting performance.  Our database applications tend to be more I/O intensive than processor intensive.  I was looking for a 'Best Practices' doc saying to disable hyperthreading but I'm not able to find anything specific.


Question by:BradyDBA
    LVL 27

    Accepted Solution

    SQL Server and hyperthreading are not friends.


    I have it on good authority (SQL Server MVP Geoff Hiten, FYI) that hyperthreading is still misbehaving on SQL Server 2005. Evidently, hyperthreading looks like a multi-core system to SQL Server 2005 thus triggering some "soft NUMA" behavior. As Geoff says, "The real problem comes in the synchronization primitives that aren't hyperthreaded friendly (such as spinlock code). SQL 2000 had a bug that was fixed in build 910 that dealt with this issue. This bug was re-introduced in SQL 2005 when they changed the memory and scheduler to handle multi-core processors and NUMA architectures. So, in short, I would turn off HT on SQL 2005 host computers as the default. As always, your mileage may vary." Great advice,

    Hyper-Threading does not benefit typical SQL Server workloads
    Generally recommend disabling Hyper-Threading
    Potentially overloads a single core with multiple concurrent scheduler tasks
    Multiple threads can thrash the CPU cache


    Hyperthreading should not be enabled on a SQL Server server unless it has been proven to improve performance.

    Author Comment

    Many thanks.

    Featured Post

    How does your email signature look on mobiles?

    Do your employees use mobile devices to reply to emails? With mobile becoming increasingly important to the business world, it is in your best interest to make sure that your email signature looks great across all types of devices.

    Join & Write a Comment

    In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for…
    This Micro Tutorial will give you a basic overview of Windows DVD Burner through its features and interface. This will be demonstrated using Windows 7 operating system.
    The viewer will learn how to successfully create a multiboot device using the SARDU utility on Windows 7. Start the SARDU utility: Change the image directory to wherever you store your ISOs, this will prevent you from having 2 copies of an ISO wit…

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now