SQL Server 2000: Negative implications of using Affinity Mask with Hyperthreading

Posted on 2008-11-19
Last Modified: 2012-05-05
Please explain the negative implications of using Affinity Mask with HyperThreading on a SQL 2000 box.
Question by:itxtech
    1 Comment
    LVL 51

    Accepted Solution

    Well, this is interesting...

    hyperthreading is actually not a great bedfellow for SQL - simply because SQL wants to do its own queing and threading, so what we see is the processors reckon that they can do a great job through hyperthreading and SQL server reckoning it is doing a better job, so we see heavy competition in the CPU. The giveaway is context switching and if Context Switches/sec higher than 5000 per physical processor then you have some real problems and should turn off hyperthreading.

    However, not all CPU's are the exclusive use of SQL Server, and you can use the Affinity mask to help isolate / control which CPUS are available - leaving one for say the operating system and other apps... In that situation hyperthreading can help on the non-SQL processors.

    There is a really good article :

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
    INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
    Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
    In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor ( If you're interested in additional methods for monitoring bandwidt…

    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

    16 Experts available now in Live!

    Get 1:1 Help Now