5

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

  • How up to date are your statistics on affected tables? – Mark Sinkinson Jan 13 '16 at 11:06
  • I would ask you two things:- output of select @@Version and output of query from Wait stats tell me when it hurts. How much RAM is present on the system and how much is assigned to SQL Server, do you see anything serious in SQL Server errorlogs. – Shanky Jan 13 '16 at 11:16
  • If you're using a lot of CPU, you're likely going to have high CXPACKET waits. Reducing MAXDOP to lower those wait numbers may not help your overall performance and may in fact make things worse (a parallel query that times out is unlikely to run faster on fewer threads). See sqlperformance.com/2015/06/sql-performance/…. I would be more apt to troubleshoot your I/O-related waits, as they seem to indicate a disk problem, but also see sqlperformance.com/2014/06/io-subsystem/…. – Aaron Bertrand Jan 13 '16 at 12:19
  • So AMD in and of itself can be a problem, but depending on the per-chip core count, overall core count, and NUMA, so can just grabbing 16 random cores from the machine. Can you describe the machine a little better - is it a VM given 16 cores from the host? What are the specs for the other VMs on the same host? What is the VM architecture? How much memory does the machine have? Is it dynamic? What is the storage infrastructure? – Aaron Bertrand Jan 13 '16 at 12:38
  • @Aaron you said It would be more apt to troubleshoot your I/O-related waits, as they seem to indicate a disk problem so are you sayingLATCH_XX wait indicate problem with Disk subsystem. I guess latch wait is related to memory. Is it not ? – Shanky Jan 13 '16 at 12:54
5

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

5

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.

1

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.

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.