3

We are trying to determine our best MAXDOP and Cost Threshold settings for an EDW box. I setup a job to clear the wait stats before the build started and then capture the wait stats after the build was complete.

Our server is Windows 2008 and SQL 2008 Enterprise. (Yeah I know, it is are oldest box and we are working on that). There are 8 CPU and 132GB of memory for the box and 118GB max memory for SQL.

The wait stats after the run are below. Would you tweak cost threshold (Current 50) or change maxdop (Current 4).

enter image description here

  • 1
    Before you go tweaking anything, you should read this excellent post on Troubleshooting CXPACKET waits - sqlshack.com/… – Scott Hodgin Mar 21 '17 at 12:21
  • 1
    No, I would not adjust any settings based solely on wait stats. That would be like putting gas in my car based on the air pressure in the tires. – Robert L Davis Mar 21 '17 at 12:25
3

Rather than change MAXDOP or Cost Threshold based on CXPACKET waits (especially logged by that script -- it's a bit incomplete), I'd rather look at the plan cache.

In there you can see execution plans for queries (assuming your cache isn't too volatile), whether they went parallel, and what their cost is.

That way, you're changing things based on your actual queries, and not just on aggregated numbers.

If you're not sure how to do that, start here with sp_BlitzCache (full disclosure, I'm one of the authors). We'll tell you all sorts of stuff about your plan cache from the XML in there.

EXEC dbo.sp_BlitzCache @SortOrder = 'cpu'

A generic run will get you information about your top 10 plans by CPU (though you can choose other sort orders like reads, duration, and more).

Nuts

And generate overall warnings, plus some info about the age of your plan cache.

Nuts

As long as your plan cache is reasonably stable, you should be able to see fairly typical plan costs.

Since you have CTFP set to 50, you may see some serial plans with a cost near but under that, and start A/B testing them for performance with parallelism. One way to do that is Trace Flag 8649.

If you see queries with a lower cost benefit from going parallel, you may want to consider dropping CTFP down to a lower number so they stand a better chance of going parallel.

Quick edit: if your plan cache isn't helpful for some reason, you should probably invest in a monitoring tool if you're serious about performance tuning this server.

Hope this helps!

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.