THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Kevin Kline

The Perils of Hyperthreading for SQL Server

Just a quick note to send a big Thank You to Christoph Stotz of Frankfurt, Germany for his hospitality on Sunday.  Thank you, Christoph! 

Slava Oks has a rather well-known blog entry about how hyperthreading can negatively impact the performance of a SQL Server 2000 instance: http://blogs.msdn.com/slavao/archive/2005/11/12/492119.aspx.

You might be wondering if those recommendations still hold true on SQL Server 2005? 

Well, 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, Geoff!

Thanks,

-Kevin

Published Saturday, August 18, 2007 4:47 PM by KKline
Filed under: , ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

mrdenny said:

Kevin,

Something that I've noticed about hyperthreaded machines in general (not just SQL Servers) is that the virtual CPUs are great, until the physical CPU gets up to about 80-90% or so.  After that the virtual CPU may show that it's got room for lots of work, but it actually doesn't.

A lot of people that I've spoken to in the past, don't understand just how the hyperthreaded CPUs work.  They do not know that the virtual CPU is just unused CPU cycles from the physical CPU.  If that physical CPU is running hot, there probably aren't many CPU cycles free, and what few are free will be spent doing process switching to get the suspended thread to become the active thread.

If your CPUs run cool most if not all of the time you can see a small performance gain by having hyper threading enabled.  However if your CPUs run hot, you should definetly look into disabling hyperthreading.

All that of course being in addition to the parts of the SQL Code which doesn't like or understand the hyperthreading.

Denny

August 20, 2007 1:25 PM
 

Lara Rubbelke said:

Kevin,

I beg to differ with this recommendation. See my email.

September 5, 2007 4:43 PM
 

KKline said:

Hi Lara,

Please post your counter-point here so that everyone can benefit from your opinion.  

Thanks!

-Kevin

September 7, 2007 3:20 PM
 

Lara Rubbelke said:

It's not my posting.

October 2, 2007 1:52 PM
 

jchang said:

HT in the original NetBurst processors (180nm Willamette/Foster and 130nm Northwood/Gallatin) was erratic

the second generation HT in Prescott/Potomac etc was OK

supposedly HT in the dual core Itanium 2 9000 series is better because it added an important priority feature

anyways, I talked about this matter back in 2003/04 but the slides are nolonger on sql server performance.

HT benefits the call to SQL server, but not the actual SQL operation itself

so if you have a really stupid client application architecture, ie, fetching 1 row at a time, ie, making 10-20K SQL Batch Requests/sec,

the HT helps by 5-10%,

so does lightweight pooling,

but for most apps, HT does not help and may hurt

December 20, 2007 9:59 PM
 

Denis Gobo said:

A year in review, The 21 + 1 best blog posts on SQLBlog Best posts according to me, it might have been

December 27, 2007 4:11 PM
 

Merrill Aldrich said:

Common wisdom with previous generations of server hardware was that SQL Server might not benefit from

January 14, 2010 5:18 PM

Leave a Comment

(required) 
(required) 
Submit

About KKline

Kevin Kline is a well-known database industry expert, author, and speaker. Kevin is a long-time Microsoft MVP and was one of the founders of PASS, www.sqlpass.org.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement