2

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

  • On the right track, but which MS recommendation made you set MAXDOP to 4 if I may ask? And yes, CTfP of 5 is too low, this will affect that total CXPACKET % you see, personally I would set it to 50 and review the cost of execution tasks and adjust if necessary. Which SQL version are you running with those 10 cpu's, and are they 10 x vCores, or hyperthreaded? – Henrico Bekker Jun 28 '18 at 4:53
  • Hi Henrico, thanks for responding. SQL Server 2016(13.0.1745.2), I understand this is out of date and we ought to apply some patches. Is that right? 10 vCPU's, 5 cores across 2 NUMA nodes not hyperthreaded (MS recommendations: support.microsoft.com/kb/2806535) Cheers, – user3547606 Jun 28 '18 at 5:04
  • The recommendation link only applies up to SQL 2014, not 2016. I would recommend setting MAXDOP back to 0. Are you using any hard-coded MAXDOP hints in any queries? – Henrico Bekker Jun 28 '18 at 5:25
  • Thanks, I'll consider changing it to zero, correct me if I'm wrong but that will enable a query that uses parallelism to consume all 10 vCPU's? I'm not sure if we have hard-coded this anywhere, would have to consult our developer who is on A/L at the moment. – user3547606 Jun 28 '18 at 5:27
  • You can view the MAXDOP for a query's execution plan, have a look at this article than can give a better in-depth explanation - mssqltips.com/sqlservertip/4266/… – Henrico Bekker Jun 28 '18 at 5:33
6

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).

| improve this answer | |
0

What type of workload is your SQL Server running, highly OLTP (that would benefit from much less, if any, parallelism), or big heavy ETL type queries (where parallelism would be beneficial) - the reality could well be a mix of both.

If it requires parallelism then MAXDOP 4 sounds reasonable but the default cost threshold is way too low, any query over 5 is potentially going to use a parallel plan, so that would certainly be a candidate to change (50 is the widely regarded best starting point).

Once the workload is understood and the servers parallelism configured correctly you can start to analyse queries. Are they parallel because they need it or are the costs high because they need tuning with indexes etc?

| 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.