What can I do about high CXPACKET waits? We are experiencing some slowness on our MS SQL Server 2016 database, I have been using Brent Ozar's first aid kit to do some initial troubleshooting.

I am seeing a high amounts of CXPACKET wait types, out of a 17.5 hour data sample we saw 99 hours of wait across our 10 CPU's, that 55.5%!

I was hoping someone here could confirm that we should be concerned about this number and resolve it asap. We have MAXDOP setting of 4 which is accurate to MS recommendations but our CTP is set to 5 which I believe needs to be changed to 50.

Just looking for clarification before I take this information to my boss, yes, I am new to database administration and yes I am looking at other wait types but this seems to be the most significant so far.

Cheers,
Josh
Install SQL Server 2016 SP2

You mentioned you're on 13.0.1745.2.  This is an RTM version of SQL Server, and is actually out of support as of 1/9/2018.

Plus, if you install SP2 you'll get the new CXCONSUMER wait type (see here for lots of info about that).  This splits out "harmless" parallelism waits from ones that you can actually do something about.  This can help you a lot in determining whether or not CXPACKET is really a problem for your server!

Query Tuning

Check out this article from Paul Randal: Knee-Jerk Wait Statistics : CXPACKET

In it, he talks about how CXPACKET can be a normal wait type - it's always going to occur if queries are going parallel (especially since you don't have the CXCONSUMER split yet).  And he also discusses how query tuning can be a more effective solution (getting at the root cause) over adjusting MAXDOP and Cost Threshold:


  One of the common cases of unexpected parallelism is when a table scan happens where you’re expecting a smaller index seek or scan.


He goes on to say that indexing, statistics updates, etc can be used to eliminate the scan, and thus the query will be less likely to go parallel.

Change MAXDOP

If you really do need to reduce the amount of CXPACKET waits, you can reduce MAXDOP to 2 (from the current setting of 4) - but this might introduce other problems.  Of course, if you have already determined that 4 is a good setting for your system through performance testing, ignore that suggestion.

Increase Cost Threshold for Parallelism

Another option is to increase this value, to prevent some queries from going parallel entirely.  I've seen a lot of advice suggesting that 50 is a good place to start, rather than the default of 5 (example).