3

Our production server is running SQL Server 2008 SP3.

I’ve read a post on “cost threshold for parallelism” - http://www.sqlskills.com/blogs/jonathan/tuning-cost-threshold-for-parallelism-from-the-plan-cache/ And I have a few questions.

We have a relatively high amount of CXPACKET wait events on our production server (in average of 85%). The server has default values for “cost threshold for parallelism” and “Max Degree of Parallelism”

I started to explore the possibility of increasing the value of “cost threshold for parallelism”. I suspect the indeed the value is too low, but I wanted to get as much information as possible before I change anything and not just change it to 25 or 50 because that’s what the some of the internet posts suggested.

I’ve read this nice post about how to determine the value: http://sqlknowitall.com/determining-a-setting-for-cost-threshold-for-parallelism/

The median cost in my server was 63.

How can I monitor my server’s status “before” and “after” I change to settings, in order to verify the effect of the value on the system’s throughput?

Thanks in advance, Roni.

Improve this question
7
  • I would look at your wait stats before the change and then after. Do your CXPACKET waits go down? Also important to look at the procedure stats to see how many went up in execution time versus decreased.
    – Jared Karney
    Apr 15, 2015 at 15:22
  • 1
    What is the MAXDOP setting on the sql server instance ?
    – Kin Shah
    Apr 15, 2015 at 15:23
  • 1
    Also, make sure you understand that changing these settings will blow away your procedure cache so performance may get poky afterwards until your plan cache is rebuilt. Don't do this during production hours.
    – Kris Gruttemeyer
    Apr 15, 2015 at 15:40
  • 2
    @Roni - whatever you do, there should be a baseline for your server that you can compare with - either good or bad when you change key things like MAXDOP, COST THRESHOLD, etc. This way you can guage if the change was good or made things worse.
    – Kin Shah
    Apr 15, 2015 at 16:28
  • 4
    I'm going to take a contrarian perspective and say that you should eliminate other possible offenders first. What is causing CXPACKET waits? You can change MAXDOP and CTP top decrease the waits for that type, but you may not be addressing the underlying issue. Are you getting other cpu-associated waits that would indicate thread starvation? Are you getting deadlocked scheduler errors and minidumps? Are your queries going parallel and each thread is waiting for storage? Latches? You can't evaluate CXPACKET in isolation.
    – swasheck
    Apr 15, 2015 at 16:37

2 Answers 2

Reset to default
-1

If you have a lot of CXPACKET waits it is possible that reducing setting the MaxDOP is appropriate and will help. You should not change the cost threshold for parallelism setting assuming you have a NUMA machine (which you most likely do).

MaxDOP: https://support.microsoft.com/en-us/kb/2806535

...and I read somewhere that cost threshold needn't be changed but am still trying to track that link down :(

In any case, focus on MaxDOP.

Improve this answer
2
  • Did you read the question OP said she has default value for max degree of parallelism and you are asking to decrease it changing max dop after seeing CXPACKET wait is knee jerk reaction. You need to see other waits which are just after CXPACKET
    – Shanky
    Apr 15, 2015 at 19:37
  • If he has the default, is it right?
    – user41207
    Apr 15, 2015 at 19:53
-1

Thanks for all the helpers. I've created a base line from the below link, to check my instance before and after the parameter change: http://www.sqlservercentral.com/scripts/baselines/96797/

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.