Post reply

degree of parallelism

  • April 1, 2026 at 6:27 pm

    #4759763

    Team,

    I'm trying to figure out what will be the best recommendation to configure max degree of parallelism say if I have 4 CPUs

    I can use 0 to use all CPUs but what will be the best practice to use

    Thanks

     

    • This topic was modified 2 weeks, 4 days ago by Davekahn.

  • April 2, 2026 at 12:28 pm

    #4760116

    Sadly, there's not a hard and fast rule on this, other than, leaving it at 0 is generally a poor choice. Setting it to 1 is actively a bad choice. So, something in between is better.

    First though, change the default Cost Threshold for Parallelism from 5 to something a LOT higher. Best way to pick a value is to get your median cost on execution plans (query cache or query store for that) and then go three standard deviations higher to set the value (mathematically significant, no, I can't explain that in detail). Alternatively, start at about 40 on an OLTP system and 60 on an analytics system and see if that works for you (horrible way to do it, but shockingly, this is what most people do).

    With that in hand, I'd lean towards making sure that no one query can use all the CPU, so with 4, set it to 3. That said, other advice is to drive it off your numa nodes. None, set it to  4 or less. Eight or less, set it to the number you have. 16 or more, set it to 16.

     

    "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 2 posts - 1 through 2 (of 2 total)

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