Sql-server – do about high CXPACKET waits

maxdopparallelismsql serversql-server-2016

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

Best Answer

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

Related Solutions

Sql-server – CXPACKET Waits performance tune for SQL Server 2008

There is a lot of misunderstanding about CXPACKET. CXPACKET isn't the cause of your problems, it is a side effect. What CXPACKET means when you see it is that that thread of a parallel query is waiting for another thread of that query to do something. Just because you see a lot of CXPACKET waits doesn't mean there's a problem with the query, it means that there is a problem somewhere else. When you see CXPACKET waits you need to look at the other threads of the SPID and see what other waits are besides CXPACKET. That other wait is the problem.

To your specific issue, the reason that the run times are so crazy is probably because the SQL Server is generating a different plan on some days because the statistics are out of date (making the job run long). Then you either manually update statistics (or via a job) or auto stats kicks in and then the plan gets better and the job runs quickly again.

Once you've solved the stats problem you can start looking at other reasons why the job is running slow. The fact that you only have a single disk is not helping any for sure.

Sql-server – High PageIOLatch_SH Waits with High Drive Idle times

Is there memory pressure on your server? The buffer pool may be filling up causing a lot of paging out to disk.

SELECT cntr_value AS [Page Life Expectancy]
FROM sys.dm_os_performance_counters
WHERE [object_name] = N'SQLServer:Buffer Manager'
AND counter_name = N'Page life expectancy' OPTION (RECOMPILE);

-- Is this a NUMA system?
SELECT
  CASE COUNT(DISTINCT parent_node_id)
     WHEN 1
         THEN 'NUMA disabled'
         ELSE 'NUMA enabled'
  END
FROM sys.dm_os_schedulers
WHERE parent_node_id NOT IN (32, 64);

Also, check the number of rows returned and the hierarchy in your where statements. You may be returning more rows than you require.