How to reduce huge CXPACKET & LATCH_EX (ACCESS_METHODS_DATASET_PARENT) wait times?

Question

Problem

We have been experiencing high levels of user disruption due to SQL timeouts accross our systems since the beginning of the year.

The SQL-Server instance in question has very high CPU usage (higher than 90% on all 16 cores all the time) during business hours.

We have also noticed very high wait times: The combination of CXPACKET & LATCH_EX accounts for about 97% of all waits. This is split about 50/50 between CXPACKET & LATCH_EX.

The non-buffered latch wait accounting for the vast majority (>95%) of LATCH_EX is ACCESS_METHODS_DATASET_PARENT.

This suggests the problem is to do with parallelism.

An example of the scale of wait times is:

CXPACKET : 332,301,799 ms
LATCH_EX : 267,955,752 ms
PAGEIOLATCH_SH : 2,955,160 ms

This was for the period between 08:00-16:24 on Jan 11th.

Options under consideration

1) Change MAXDOP from 0 to something between 4 and 8

2) Modify the cost threshold of parallelism from 50 to a higher number

Suggestions most welcome on how to ease the very high CPU load we are seeing, and reduce timeouts, in particular whether the proposed course of action is wise, and which numbers to change MAXDOP and cost threshold of parallellism to.

Background information

• SQL-Server 2008 R2 running on AMD Opteron 6180 SE, of which 16 cores are given to this instance of SQL-Server.

• Type of workload: something of the order of 800 connections at the same time during business hours; majority OLTP type workload with some OLAP mixed in.

• Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64) ... Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1). Memory is appx 128 Gigs between 24 cores. 16 of the cores are available to this instance

Answer 1

majority OLTP type workload with some OLAP mixed in

This is the problem, not the CXPACKET. Parallelism is a symptom, not a cause. Your 'some' OLAP workload is doing scans which trigger parallelism, which cascades into exchange wait times, likely buffer pool pollution and possible blocking (OLTP workload blocks behind OLAP scans).

If the OLAP workload is well understood and absolutely critical, then you can consider adding appropriator covering indices for it. But is an ever uphill battle. I would much rather see the OLAP workload, with its damaging scans, move onto a dedicated box. Newer versions (SQL Server 2014) have readable secondaries and columnstores, both exquisite at serving analytical/ad-hoc/OLAP workloads.

For SQL Server 2008 R2 I would consider log shipping or replication (although I reckon none is 'perfect').

Short term: you have a performance problem and you need to analyse it appropriately. Read How to analyse SQL Server performance. Identify the query or queries that cause most damage (see identifying problem queries. Only then, after you identified the actual problem, can solutions be recommended.

NB: LATCH_EX on ACCESS_METHODS_DATASET_PARENT is not about IO at all. It is strictly related to parallelism, as is the latch the parallel scans 'child' threads must acquire on the parent thread to allocate the scan range for that child. Contention on it indicates the parallelism is inefficient (is doing more 'homework' than actual useful work). Partitioning would exacerbate this symptom, specially unaligned partitioning (as parent/child datasets are set per partition). Bad cardinality estimates (outdated stats?) may also be to blame, conducting to parallelism when not necessary. On and all my advice is the same: identify the actual problem query(s).

Answer 2

As mentioned, the CXPACKET is often a misleading wait type in sense that high values are not always bad, and quite often these high values are just the indicator but not the real problem.

So, examining other wait types that are accompanying the CXPACKED would be a good start point in troubleshooting. It is advisable to check the following:

• Check the Cost Threshold for Parallelism (CTFP) and make sure that the value used is appropriate for your system

• Check whether the CXPACKET is accompanied with a LATCH_XX (possibly with PAGEIOLATCH_XX, like in your case, or SOS_SCHEDULER_YIELD as well). If this is the case, then the MAXDOP value should be lowered to fit your hardware

• Check whether the CXPACKET is accompanied with a LCK_M_XX (usually accompanied with IO_COMPLETION and ASYNC_IO_COMPLETION). If this is the case, then parallelism is not the bottleneck. You'll need to troubleshoot those wait stats to find the root cause of the problem and solution

Finally, read the Troubleshooting the CXPACKET wait type in SQL Server article to get more in depth info on this, and to check what real world scenarios are the most common ones.

Answer 3

as Remus says you have a performance problem, probably because of mixed workload and poorly tuned sql statement. In my experience the best way to solve is to use a performance/tuning approach on sql queries. If this is not possible or if you have to manage a critical situation you can lower the highest value limit for the MAXDOP parameter. To find a better value you can execute the top N sql query for events you reported (in particular LATCH_EX) using the maxdop query int:

SELECT *
FROM Sales.SalesOrderDetail
ORDER BY ProductID
OPTION (MAXDOP 1)

You can start with MAXDOP=8 and lower the value until when the execution time decreases or does not change... (yes, the execution time can decrease or remain the same). When you find an optimal value, set it at instance level. In my experience this approach can help, but keep in mind: this is a workaround, not a solution for your performance problem.