Post reply

Hyperthreading and Affinity Mask

  • 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.

  • March 23, 2005 at 2:38 pm

    #548605

    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.

  • March 24, 2005 at 4:09 am

    #548691

    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/

  • March 24, 2005 at 6:49 am

    #548728

    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.

  • March 24, 2005 at 8:53 am

    #548776

    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."

  • March 24, 2005 at 9:29 am

    #548794

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


    * Noel

  • March 29, 2005 at 6:30 am

    #549312

    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/

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

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