January 4, 2008 at 1:56 am
Hello,
We use currently HP machines with double or quad cores (2 or 4 socket). So we have 4 or 8 CPUs without HT. But this is enabled by default and I could not find any advice about this.
What's your opinion about this setting?
MCP ID# 1115468 Ceritified Since 1998
MCP, MCSE, MCP+I, MCSE+I, MCSA,
MCDBA SQL7.0 SQL 2000, MCTS SQL 2005
January 4, 2008 at 2:04 am
In my opinion, hyperthreading ahould not be enabled on a server that will be running SQL. Multi cores are fine.
Hyperthreading doesn't give two independent cores though SQL assumes them to be. The big problem is that the two virtual cores share L1 cache. If either 'core' is doing a lot of data movement (like the checkpointer or the lazy writer) then the other 'core' will be stalled with cache misses.
Google Slava Oks and Hyperthreading and you should eb able to find the article he wrote about SQL and hyperthreading (Slava's on the SQL dev team)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 4, 2008 at 5:56 am
We've gone both ways. If you do leave the hyperthreading on, you can't treat it like a real multi-core machine. So for example we have two processor machines with hyperthreading, but we throttle SQL Server to only use two of the "four" available processors. It's still less than a perfect solution because it could use "two" processors that are actually on the same chip. We've only done this on dev boxes. In production, we're following Gail's advice. In production we only run single or a very few instances on a physical box as opposed to dev where we run a number of instances on a physical box. Having the illusion of multiple processors is useful in the dev environment where as it would be a showstopper in production.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 7, 2008 at 1:43 pm
the answer to this is very simple and I'm surprised sometimes that its not so well known. HT threads run at less "power" than a core, say around 70% less - sql server doesn't know this and allocates parallel executions between all threads - as the HT threads don't process at the same speed or strength it means the parts of the parallel plan don't complete at the same time - this will lead to waits and eventually blocking. If you never use a parallel plan and/or have paralleism turned off all well and good. There's also issues with workers in x64 editions especially, there's mention of this and a link on a post I just did http://sqlblogcasts.com/blogs/grumpyolddba/
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
January 8, 2008 at 5:19 am
I had never heard of that before. Extremely useful information. Thanks for putting it up. Makes the decision to keep hyperthreading off production even better.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply