[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQL 2005 and hyperthreading

Posted on 2007-10-10
2
Medium Priority
?
1,058 Views
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.

Suggestions?

Thanks.
0
Comment
Question by:BradyDBA
2 Comments
 
LVL 27

Accepted Solution

by:
ptjcb earned 500 total points
ID: 20050843
SQL Server and hyperthreading are not friends.

http://blogs.msdn.com/slavao/archive/2005/11/12/492119.aspx

FROM http://www.sqlmag.com/Article/ArticleID/96850/sql_server_96850.html

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,


FROM http://209.85.165.104/search?q=cache:0H8WBxgNoKYJ:www.sqlservices.com/documents/SQL_64bit_Presentation_20060726.ppt+SQL+2005+64-bit+hyperthreading&hl=en&ct=clnk&cd=10&gl=us
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

FROM http://blogs.digineer.com/blogs/larar/archive/2006/11/13/maximum-degree-of-parallelism-and-hyperthreading.aspx

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

Author Comment

by:BradyDBA
ID: 20051172
Many thanks.
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

Ever visit a website where you spotted a really cool looking Font, yet couldn't figure out which font family it belonged to, or how to get a copy of it for your own use? This article explains the process of doing exactly that, as well as showing how…
The article covers five tools all IT professionals should know about, as they up productivity by a great deal!
In this video, we discuss why the need for additional vertical screen space has become more important in recent years, namely, due to the transition in the marketplace of 4x3 computer screens to 16x9 and 16x10 screens (so-called widescreen format). …
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …

834 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