Good Maxdop value in SQL 2014

  • I have a server with 2 physical CPU's, hyper threaded ratio at 12 which makes it 24 logical CPU's.

    Currently, Maxdop is set to default.

    Should I be making a change as per the MS best practice

  • IMHO yes. Let others know what you're doing first so they have the right of refusal, but otherwise while other DBAs will tell you to test thoroughly first, this is non trivial and I am of the opinion that you should always START by enforcing best practices and only modify from there onwards. However that's in part because we manage hundreds of servers.

    Get coreinfo.exe from Microsoft and use it to print out the core/socket/numa/hyper threading configuration. Pick the lowest of cores per socket / 2 for hyperthreading, or the number of cores per numa node / 2 for hyperthreading, or 8.

    We also set the cost threshold for parallelism. 5 is extremely low. We set 25 but others do 50. There is a very cool script to go through the plan cache and show you which buckets all of the queries fall into so that you can tweak, and 25 seems to be very reasonable for us.

  • codykonior (8/27/2015)


    IMHO yes. Let others know what you're doing first so they have the right of refusal, but otherwise while other DBAs will tell you to test thoroughly first, this is non trivial and I am of the opinion that you should always START by enforcing best practices and only modify from there onwards. However that's in part because we manage hundreds of servers.

    Get coreinfo.exe from Microsoft and use it to print out the core/socket/numa/hyper threading configuration. Pick the lowest of cores per socket / 2 for hyperthreading, or the number of cores per numa node / 2 for hyperthreading, or 8.

    We also set the cost threshold for parallelism. 5 is extremely low. We set 25 but others do 50. There is a very cool script to go through the plan cache and show you which buckets all of the queries fall into so that you can tweak, and 25 seems to be very reasonable for us.

    Thanks. Can you suggest a value for my case?

  • Is this an OLTP system ? the defaults are the best usually when you don't know what value to set. SQL automatically decides when to use a parallel plan and it normally doesnt require any input from the user.

    If you set MAXDOP at the server level it applies to all queries executed on the sever, instead I would suggest you had a query hint when needed and leave the rest at defaults.

    for an OLTP system you very rarley need parallelism for DW you almost always need parallelism but how many CPUs to use it best left for SQL ti decide.

    Jayanth Kurup[/url]

  • In addition to changing the Maxdop, make darned sure you change the cost threshold for parallelism. The default of 5 is insanely low.

    "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

  • How many physical cores per processor? 6?

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • GilaMonster (8/31/2015)


    How many physical cores per processor? 6?

    2 physical CPU's, hyper threaded ratio at 12 which makes it 24 logical CPU's.

    Is there a script I can use to pull the information you are asking? I used one already

    SELECT cpu_count AS Logical_CPU_Count , cpu_count / hyperthread_ratio AS Physical_CPU_Count FROM sys.dm_os_sys_info ;

    As per this, we have 2 physical CPU's and 24 Logical CPU's

  • Jayanth_Kurup (8/27/2015)


    Is this an OLTP system ? the defaults are the best usually when you don't know what value to set. SQL automatically decides when to use a parallel plan and it normally doesnt require any input from the user.

    If you set MAXDOP at the server level it applies to all queries executed on the sever, instead I would suggest you had a query hint when needed and leave the rest at defaults.

    for an OLTP system you very rarley need parallelism for DW you almost always need parallelism but how many CPUs to use it best left for SQL ti decide.

    This is a OLAP server..

  • How many physical cores per processor? Don't look at SQL, check the specs of the processor you have.

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • GilaMonster (9/1/2015)


    How many physical cores per processor? Don't look at SQL, check the specs of the processor you have.

    I see 2 physical processors when I run the msinfo.exe.

    6 cores per processor making it 12 logical processors.

  • So 6 then.

    In that case, you can try 6, 9 or 12 as maxdop, see which works best for you (calced as 1x, 1.5x and 2x the number of physical cores/NUMA node)

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • GilaMonster (9/2/2015)


    So 6 then.

    In that case, you can try 6, 9 or 12 as maxdop, see which works best for you (calced as 1x, 1.5x and 2x the number of physical cores/NUMA node)

    Somewhat related : How would this differ if we're running on a (Hyper-V) VM?

    Mike Hinds Lead Database Administrator1st Source BankMCP, MCTS

  • It wouldn't, though identifying cores/NUMA node might be a tad more challenging, and if the VM admin's screwed up the config, could be harder still.

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 13 posts - 1 through 12 (of 12 total)

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