Jan
1
2014

SQL Server CXPACKET waittype and MAXDOP settings – Tips

 

Recently while troubleshooting performance issue on a server, found that the CXPACKET waittype is high,

 

WaitType

Wait_S

Percentage

AvgWait_S

AvgRes_S

AvgSig_S

CXPACKET

1402132.06

56.82

0.0067

0.0063

0.0004

 

CXPACKET waits in the server are not an immediate sign of problem; they may indicate another problem, associated with one of the other high value wait types in the instance for e.g. Queries are executing with parallelism

 

Now when dealing with CXPACKET waittype do following:

  • Check Parallel operations are taking place or not
  • Correlate it with PAGEIOLATCH_SH waits
  • Verify that server-wide MAXDOP is NOT set to 1 (exceptional is Sharepoint case where MS recommends MAXDOP setting as 1 Ref. Link)

 

Possible cases telling why CXPACKET waittype is high:

  • Parallelism occurring
  • Table scans are being performed because of missing non-clustered indexes
  • Incorrect query plan
  • Out-of-data statistics causing skewed work distribution

 

Make sure following is performed to bring down CXPACKET waittype,

  • Make sure statistics are up-to-date and appropriate indexes exist
  • Consider MAXDOP at instance level, but beware of mixed-mode workloads
  • Consider MAXDOP = physical cores per NUMA node
  • Consider using Resource Governor for MAX_DOP
  • Consider setting ‘cost threshold for parallelism‘ higher than the query cost shown in the execution plan

 

As per Microsoft, following are recommended settings for MAXDOP and later need to fine tune as per environment as nothing is standard in this case,

SQL Server 2005 and later versions

•For servers that use more than eight processors, use the following configuration:

MAXDOP=8

•For servers that use eight or fewer processors, use the following configuration:

MAXDOP=0 to N

In this configuration, N represents the number of processors.

•For servers that have NUMA configured, MAXDOP should not exceed the number of CPUs that are assigned to each NUMA node.

•For servers that have hyper threading enabled, the MAXDOP value should not exceed the number of physical processors.

•For servers that have NUMA configured and hyper threading enabled, the MAXDOP value should not exceed number of physical processors per NUMA node.

Note: Use these same guidelines when you set the max degree of parallelism option for Resource Governor workload groups.

Also, the maximum value of 8 that is mentioned in these guidelines is applicable for typical SQL Server activity and the overhead for the exchange operators that are used in parallel query plans. You can vary this maximum value, depending on your specific application patterns and the concurrent activity on the instance of SQL Server.

 

Useful TIP: There are queries written by DBA’s to find MAXDOP value using the CPU count from DMV’s, please find link to one forum where you can get two such queries to test out- Forum link (Difference between queries is, one checks for Hyper threaded environment and other doesn’t, so test as per requirement)

 

To check Processor information, download COREINFO utility from sysinternals webpage

 

While investigating under my scenario, found that MAXDOP was set to 8 whereas server cores are found to be 4, hence fine tuning of this setting is suggested along with setting appropriate COST THRESHOLD FOR PARALLELISM

 

As quoted from article “The ins and outs of MAXDOP“,

How to know if the current “max degree of parallelism” is contributing substantially to a current performance or resource constraint problem?

If your Process.SQLServr.%Processor Time/# of logical processors value is much higher than normal/expected AND wait stats shows more than 5% of your waits are on CXPackets, you may want to test lower (or non-zero) values of “max degree of parallelism”. You find connections that have currently running parallel queries by looking for SPIDs with multiple ECIDs in sys.sysprocesses. Query plans that are eligible for parallelism will have the Parallelism operator. If you decide your current degree of parallelism might be negatively impacting performance, use the information above to estimate what may be a good starting point for your particular instance and test/baseline/tune from there. No restart is required after you change the MAXDOP value; new queries automatically use the new value (unless overridden by a hint in the query).

 

 

To confirm further, Perfmon can also be set to check the %Processor usage as per below chart check if it reflects bottleneck or use free PAL tool for quick performance analysis,

 

Object

Counter

Threshold

Comments

Processor

% Processor Time

< 80%

Amount of total CPU usage across all processors

Processor (sqlservr)

% Processor Time

< 80%

Amount of total CPU usage across all processors

Processor

%Privileged Time

< 30% of Total %Processor Time

% Privileged Time is the percentage of elapsed time that the process threads spent executing code in privileged mode.

Processor

% User Time

< 80%

Amount of total CPU usage in user mode across all processors

System

Processor Queue Length

< 4 per CPU

For standard servers with long Quantum’s
<= 4 per CPU Excellent
< 8 per CPU Good
< 12 per CPU Fair

 

 


ßà

 

Below are some very good links which tells in detail about MAXDOP setting and how to deal with it,

The ins and outs of MAXDOP by Cindy Gross

Forum discussion on “MAXDOP setting – performance improvement”?

Case Study: Part 1: CXPACKET Wait Stats & ‘max degree of parallelism’ by Jimmy May’s

Recommendations and guidelines for the “max degree of parallelism” configuration option in SQL Server

SQL Server support in a hyper-threaded environment

Cost threshold for parallelism

What MAXDOP setting should be used for SQL SERVER?

NUMA Node & Max DOP Settings

 

A deep dive and very useful article to understand MAXDOP and parallel query plan architecture and how SQL behaves with these settings:

Understanding and Controlling Parallel query processing – MAXDOP,Cost threshold,Parallel execution plans,Operators

 

Now, if you are looking to fine tune only few of slow running queries then below post may come handy, it is for beginners,

Quickly energize your slow running SQL query

 

This article is not written in terms of explaining how to troubleshoot performance problem, but for a specific case where MAXDOP was enabled and not seems to be set correctly and leading to processor bottleneck, in your case setting might be different, hence further investigation may be required.

Download PDF

About the Author: Nitin G

Indian born, trekker, biker, photographer, lover of monsoons. I've been working full time with SQL Server since year 2005 and blogs to post the content aquired during my research on new topics or fixing issues faced by me as a DBA while working in different kind of projects, hope some of my posts may helps others in SQLDBA community. Everything you read on my blog is my own personal opinion and any code is provided "AS-IS" with no warranties!

Leave a comment

Subscribe to this blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 263 other subscribers

Translate this blog!

EnglishFrenchGermanItalianPortugueseRussianSpanish

Calender

December 2020
M T W T F S S
« Nov    
 123456
78910111213
14151617181920
21222324252627
28293031  

View Post by Categories

%d bloggers like this: