SQL Server : Parallelism vaules best practice

  • Hi,

    We are using SQL Server 2012 having Always on High Availability setup ( Active - Passive nodes) with 2 TB database size.
    CPU : 8 virtual CPU
    Memory : 128 GB - OS and 80 GB to SQL server 
    What vaule should be set to Parallelism for below paramter:
    Cost Threshold For Parallelism:
    Locks:
    Max Degree of Parallelism :
    Query Wait:

    Can you please let us know the best possible vaule to optimal performance of DB server?

    Thanks & Regards,
    santosh

  • santosh.mane - Wednesday, December 19, 2018 5:21 AM

    Hi,

    We are using SQL Server 2012 having Always on High Availability setup ( Active - Passive nodes) with 2 TB database size.
    CPU : 8 virtual CPU
    Memory : 128 GB - OS and 80 GB to SQL server 
    What vaule should be set to Parallelism for below paramter:
    Cost Threshold For Parallelism:
    Locks:
    Max Degree of Parallelism :
    Query Wait:

    Can you please let us know the best possible vaule to optimal performance of DB server?

    Thanks & Regards,
    santosh

    It depends on your workload and performance issue. You can increase the CTP and others default is fine. If you start experience something, you have to capture some data and analyze to make a benchmark.

    Muthukkumaran Kaliyamoorthy
    https://www.sqlserverblogforum.com/

  • I agree w/Muthukkumaran

    It  really depends on your workload.  For example I worked at place where maxdop  was set at 1, this was due to too much parallelism recommended by the vendor after some analysis. 

    The best practice is set it to 8 procs no matter how many vCPUs or physical procs you have that is the "Sweet" spot...least that's what it was in a couple of years ago...I haven't read the latest if it's changed with SQL 2016/2017 ?  For 2012 that was the sweet spot when performing some testing on a system with greater than 16 CPUs

    --------------------------------------------------
    ...0.05 points per day since registration... slowly crawl up to 1 pt per day hopefully 😀

  • Go with Muthukkamaran's advice. Change the Cost Threshold to a higher value and then leave everything else at the defaults until you see a reason to change through monitoring.

    The value you should change the threshold to is determined one of two ways. Take your existing load, determine the average cost of all the plans in the system (using the plan cache or query store(2016+only)), then go two-three standard deviations above that average. This will be the best possible setting for your system. Alternatively, you take the shortcut and set it to 50 for an OLTP system and 30 for a reporting system and then adjust from there as needed.

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

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