Post reply

Looking for recommendation on "cost threshold value"

  • February 15, 2020 at 9:34 am

    Go to Answer

    #3726152

    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

  • February 16, 2020 at 10:10 am

    #3726235

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

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

  • February 16, 2020 at 12:05 pm

    #3726239

    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/

  • February 16, 2020 at 2:49 pm

    Answer

    #3726247

    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.

    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)

  • February 17, 2020 at 7:49 am

    #3726324

    Thank you all for the suggestions.

  • February 20, 2020 at 11:58 am

    #3727318

    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 4 years, 9 months ago by  Guy Bishop.

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

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