March 24, 2015 at 1:03 pm
Hi SQL Gurus,
I have CXPACKET is very high > 70% (wait). Most of all batches are call thru RPC (webserver). If I want to the MAXDOP to 8 or more should I changed on server level or tell developers/application team to add 'OPTION(MAXDOP 8)'. Where should I start from?
Note: I have 40 logical cores and MAXDOP Is 0 right now.
March 24, 2015 at 1:35 pm
How are you sure that the high CXPACKET wait type is from the RPCs?
However, you could reduce the high parallelism by increasing the Cost Threshold For Parallelism (e.g. set CTFP to 50) option in the sp_confiugre. I suggest fist trying with CTFP before lowering the maxdop to some value.
Igor Micev,My blog: www.igormicev.com
March 24, 2015 at 2:42 pm
CXPACKET is just an indication of parallel processing going on. As a wait, it's not indicative of anything other than that, you have parallel query processing. You need to look at other CPU counters to determine if you're under stress from the parallel processing.
And, I agree, first step, change the Cost Threshold for Paralllelism. The default value of 5 is ridiculous. You could query the plans in cache and get their costs, look for the median or the mean and use that value. Or, start with 50 as was already suggested.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 26, 2015 at 6:29 am
Igor/Grant,
Almost all SP are called by Web server. I have CTFP is set 25. I don't see any CPU pressure or slowness, but But when I check 'performance_dashboard_main' it says ' System performance may be degraded because of excessive waits happening on the server....'
when I check 'wait' it show 'parallelism' wait time is above 70%.
March 26, 2015 at 7:19 am
CXPacket waits are an indication that queries are running in parallel. That's it. It is not by itself indicative of performance problems, it is not by itself indicative of high CPU usage, It's not by itself indicative of anything other than that you have queries running in parallel.
If you still feel that it's too high, take traces of your server at busy times. I prefer SQLTrace for SQL Server 2008, but I know Grant will recommend Extended Events instead. 🙂
Identify the long-duration and/or high CPU queries and see if you can tune them. It'll improve performance overall, though I won't promise it'll drop the CXPacket waits down.
If you're going to play with MAXDOP, the figure I use (based on advice from Adam Machanic) is 1.5*(number of physical cores in a NUMA node).
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 26, 2015 at 3:18 pm
I agree with everything that Gail said.
Well, except that you ought to be using extended events instead of trace.
But yeah, you may be hitting waits, CXPacket waits are not, all by themselves, an indication of anything except the fact that parallelism is occuring. Look to other wait types.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 27, 2015 at 2:20 am
If you're going to play with MAXDOP, the figure I use (based on advice from Adam Machanic) is 1.5*(number of physical cores in a NUMA node).
I'm curious to see that advise and some experiments for it. Is there a link somewhere? I couldn't find it. Thanks.
Igor Micev,My blog: www.igormicev.com
March 27, 2015 at 2:47 am
Igor Micev (3/27/2015)
If you're going to play with MAXDOP, the figure I use (based on advice from Adam Machanic) is 1.5*(number of physical cores in a NUMA node).
I'm curious to see that advise and some experiments for it. Is there a link somewhere? I couldn't find it. Thanks.
It was based on a discussion that we had, he hasn't, to my knowledge, blogged it.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply