Looking for recommendation on "cost threshold value"

  • vsamantha35

    SSChampion

    Points: 11158

    H All,

    What is the recommended value for setting sp_configure setting "Cost Threshold For Parallelism" for an OLTP workload instead of default value 5?

    Regards,

    Sam

  • Site Owners

    SSC Guru

    Points: 80378

    Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • frederico_fonseca

    SSChampion

    Points: 14487

    you should know better by now that such a basic question without some google investigation is not getting you nowhere fast.

    lots of info about that subject already that you should have investigated.

    as most here will say - it depends - no fixed value is best - can be 20, can be 50, can be 200.

    and value on its own if not associated with changes to max dop and to statistics may be useless

    see one thread on it - https://www.brentozar.com/archive/2017/03/why-cost-threshold-for-parallelism-shouldnt-be-set-to-5/

  • Jeff Moden

    SSC Guru

    Points: 996088

    To Frederico's point, here's my entry on the post he provided a link to above...

    Just my entry on this post…

    When I first laid eyes on the servers at where I currently work full time, I changed them all from 5 to 25. It was a pretty good guess because if I go much above that on the big server (combined OLTP and heavy processing jobs), things started to slow down substantially.

    I tried the same #, 25, at a different company I was part-timing at and it didn’t make much of a difference until I got close to 50 on one server and worked fine at 25 on another. It seems to be what types of work load you have going on and what the “split” between OLTP and big processes is.

    As with all else in SQL Server, “It Depends” and “Must look eye”! ??

    About the only thing that is certain when it comes to the CToP setting is that a setting of 5 has about a 99.99% chance of being the wrong setting for your system for all the reasons that Erik Darling posted in the article at the aforementioned link including what your MAXDOP setting is (as another variable in the mix).

    For me, "tuning the engine by ear" worked.  I used PerfMon to watch the system to watch for CPU usage, file usage, blocking, and a couple of other settings as I tweaked the CToP setting in a kind of "binary search" between 5 and 100 and then settled for the "best" value rounded up to the nearest 5 just to keep people from asking questions about what made me so sure that (for example) 23 was going to be the best setting.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • vsamantha35

    SSChampion

    Points: 11158

    Thank you all for the suggestions.

  • Guy Bishop

    SSC Journeyman

    Points: 91

    You can query the plan cache for estimated plan costs. You can use this to decide on a suitable cost threshold value for your instance.

    Grant Fritchey did a good article on it;

    https://www.scarydba.com/2017/02/20/estimated-costs-queries/

    • This reply was modified 1 month, 2 weeks ago by  Guy Bishop.

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

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