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

Posted on 2008-11-19
Medium Priority
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

Mark Wills earned 1500 total points
ID: 23002417
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 : http://technet.microsoft.com/en-au/magazine/cc137784.aspx

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …

839 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