Hyperthreading and Affinity Mask

Question

Hyperthreading and Affinity Mask

ramses2nd

Hall of Fame

Points: 3720
More actions
March 23, 2005 at 1:25 pm

#113628

I have a 4 cpu server set up with hyperthreading, so the OS and SQL Server see 8 cpu's. First question, is this a good thing for a server running SQL Server 2000 sp3? Second, should I set up affinity masking to utilize all 8 servers? I have use all servers checked in the properties window under processors, and have confirmed they are all being hit using perfmon.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

rhunt SSCertifiable Points: 5868 More actions · Answer 1

You have to use affinity mask to use all 8 logical CPU's? That doesn't sound correct to me. Based on the current setting to use all CPU's, SQL should probably manage this for you properly.

Typically affinity masks are setup to force certain processes to use specific processors (and specifically not use others). My general rule of thumb (yes there can be exceptions) is this: If you have processes on a server competing with Sqlserver.exe, you would be better served to move those competing process (or sqlserver) to a dedicated server.

Anyway, that's starting to move away from your question.

I would leave your setting as is. IMHO.

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 2

I never touch this - been asked many times though - e.g. "wouldn't it be better to limit SQL Server to the first 6 procs and leave the other 2 for the o/s"

where do they get these questions <grin> unless the box isn't a dedicated sql box as suggested above leave it alone.

The only thing you might want to consider, especially if you have applications with less than optimal sql code, is the impact of parallelism. ( Poor code can force parallel plans which actually run slower )

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/

Rainer Plischke Mr or Mrs. 500 Points: 506 More actions · Answer 3

This also strongly depends on the OS. We had a similiar setup with Windows 2000 and 4 hyperthreaded CPUs and once in a while experienced _massive_ performance problems (as it turned out this were process starvations). According to MS it is not recommended to use Win2K with hyperthreading, especially not with SQL Server. With Windows 2003 it shouldn't be a problem, then this strongly depends on the other stuff you're doing on that box. If SQL Server is the only important service on that machine I'd use 7 cpus for SQL and leave one for everything else.

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 4

Based on reading other threads/discussions/websites it seems the only reason to use 'affinity' on a SQL server that is dedicated to 'only' SQL Server would be in the event that you have multiple instances on the server.

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

noeld SSC Guru Points: 96590 More actions · Answer 5

affinity mask is a very specialized setting and should only be used in extreme situations and very tunned systems. IMO: Leave this alone

* Noel

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 6

When you set the max cpu's for sql server it limites sql server to this number of processors, "everything else" can still use all the procs if "everything else" is smp aware.

I have only ever run dedicated sql boxes but have run HT on w2k adv server and w2k server without any problems since P4's with HT were released ( subject to poor code generating unwanted parallelism < grin > )

I totally agree with noeld, unless instructed by Microsoft " don't touch !!"

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/