June 10, 2011 at 7:25 am
Hi All,
Wait stats on a slow running data warehouse is showing 47% on CXPACKET, the next closest wait is 6%.
Current MAXDOP setting are at the default of 0. What would you recommend for a 12 core @ 2.2 GHz server with 32GB of memory?
Thanks
June 10, 2011 at 9:09 am
If all your index stats are up to date then I'd actually set it to 1, or at least find the offending statement and add the option maxdop 1 to the end of it. I've had similar problems with poor parallelism choices by the engine in both 2005 and 2008R2 (on very similar hardware) causing really ridiculous cxpacket waits. Never been able to find a really good explanation as to why it would get it so wrong. Instead we take explicit control of *inter* (instead of intra) query parallelism through SSIS control flows.
June 10, 2011 at 9:49 am
I would not recommend setting MAXDOP to 1 for most systems. Instead, I'd experiment with bumping up the cost threshold for parallelism. The default value of 5 is way too low in my opinion. You should test for what's right on your system, but I'd suggest starting somewhere between 35 and 50, depending on what makes you more comfortable and then go from there.
"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
June 10, 2011 at 9:55 am
Yeah, fair point. Changing the threshold didn't work well for us in several cases (although admittedly we didn't go ridiculously high with it, mainly due to frustration), so we went with maxdop hints where we needed them.
June 10, 2011 at 10:13 am
The decision for which queries to put through parallelism is all based on the cost threshold, so controlling than rather than simply turning off parallelism completely I've found to be a better approach. In general, for an OLTP system, you shouldn't be seeing queries with an estimated cost above 30. when you do, that's your issue, not the fact that it went parallel during execution.
"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
June 10, 2011 at 1:29 pm
I must admit I've always played around with the MAXDOP setting when I had high CXPACKET waits.
Grant's suggestion is something I had never considered before.
So, if I understand it correctly, raising the cost threshold for parallelism value to, say 35, means roughly that the estimated elapsed time in seconds required to run the serial plan has to exceed 35 seconds for it to be converted to a parallel plan.
Is my understanding correct?
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
June 10, 2011 at 1:38 pm
Marios Philippopoulos (6/10/2011)
I must admit I've always played around with the MAXDOP setting when I had high CXPACKET waits.Grant's suggestion is something I had never considered before.
So, if I understand it correctly, raising the cost threshold for parallelism value to, say 35, means roughly that the estimated elapsed time in seconds required to run the serial plan has to exceed 35 seconds for it to be converted to a parallel plan.
Is my understanding correct?
Yes.
One tiny additional wrinkle, the estimated cost has to exceed 35 for the query to be CONSIDERED for running parallel. It's not automatic that the query will generate a parallel plan. The storage engine makes that determination, not the optimizer. But once it's above that threshold, it's extremely likely.
"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
June 10, 2011 at 9:31 pm
If we'd gone up to 50 our results may have been different, I'll grant you. But on a system with thousands of users there's plenty of inter-query parallelism going on anyway, making use of all available cores most of the time. Allowing the engine to spread any one query across multiple cores in such a scenario seemed to open up the potential for these poor plans to come into play. This may be because the engine is "allowed" to choose a parallel plan even when the ultimate cost is below the threshold, because the decision to go parallel comes earlier than final optimization.
If the threshold works in your situation, then yes, it's certainly very reasonable advice. After all, that's what the setting is there for.
June 12, 2011 at 5:15 am
Ok guys thanks for your advice to date.
I wanted to provide somekind of update to where my findings have led me, any feedback is welcome as always.
So I posted originally as the Max Dop setting is the easy one to point the finger at when experiencing high wait times. However as always I think a combination of things aren't helping.
It's worth nothing that the entire disk subsytem is a shared SAN running RAID 5 which, I am not entirely happy about. But for the moment there are no plans to set up a RAID 10 array from our infastrstructure team!
So, lets look at the SQL Server stuff:
1. So CXPACKET wait stats are through the roof
2. Max DOP is set to 0 and the cost threshold is at the default too. These are setting which can be altered and should be tested throughly.
3. Next I viewed the sys.dm_db_index physical_stats and found a high percentage of indexes are interally fragmented. Some are also externally fragmented. So it looks like an index rebuild go be the order of the day.
4. I also noted that there is only one filegroup for all of the files. I would imagine there this is also creating a significant bottleneck when attempting to run parrelle queries.
My plan of action is as follows:
1. Rebuild all indexes
2. Split at least data and indexes onto filegroups other than the primary as a minimum
3. Re-evaluate performance and move on to Max DOP/Cost threshold for parrellism as a last resort
4. Future recommendation to have a raid 10 array and potentially create more files for temp db based on number of cores on server.
Thoughts?
June 12, 2011 at 6:06 am
aaa-322853 (6/12/2011)
Ok guys thanks for your advice to date.I wanted to provide somekind of update to where my findings have led me, any feedback is welcome as always.
So I posted originally as the Max Dop setting is the easy one to point the finger at when experiencing high wait times. However as always I think a combination of things aren't helping.
It's worth nothing that the entire disk subsytem is a shared SAN running RAID 5 which, I am not entirely happy about. But for the moment there are no plans to set up a RAID 10 array from our infastrstructure team!
So, lets look at the SQL Server stuff:
1. So CXPACKET wait stats are through the roof
2. Max DOP is set to 0 and the cost threshold is at the default too. These are setting which can be altered and should be tested throughly.
3. Next I viewed the sys.dm_db_index physical_stats and found a high percentage of indexes are interally fragmented. Some are also externally fragmented. So it looks like an index rebuild go be the order of the day.
4. I also noted that there is only one filegroup for all of the files. I would imagine there this is also creating a significant bottleneck when attempting to run parrelle queries.
My plan of action is as follows:
1. Rebuild all indexes
2. Split at least data and indexes onto filegroups other than the primary as a minimum
3. Re-evaluate performance and move on to Max DOP/Cost threshold for parrellism as a last resort
4. Future recommendation to have a raid 10 array and potentially create more files for temp db based on number of cores on server.
Thoughts?
I like it all except #3. I really would change that cost threshold. There are situations where killing parallelism with MaxDOP is the answer, but most situations that default setting is just too low.
"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
June 12, 2011 at 9:47 am
I'd tend to agree with Grant. Raise the threshold. You don't want queries unnecessarily grabbing codes to execute in parallel if they aren't sufficiently complex.
June 13, 2011 at 12:35 pm
What about the case were you have multiple client databases on a single instance. You may have one client database and their queries that is overusing the CPU and starving the other databases even after adjusting the cost threshold.
June 13, 2011 at 12:38 pm
patrick.doran (6/13/2011)
What about the case were you have multiple client databases on a single instance. You may have one client database and their queries that is overusing the CPU and starving the other databases even after adjusting the cost threshold.
This is when you look to split instances in preparation to get a database with that volume of usage its own hardware.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
June 13, 2011 at 12:40 pm
I'd agree with Craig. This is why you separate clients into separate databases. When one starts to use too many resources, you move them to their own instance/server.
June 13, 2011 at 12:55 pm
Yeah that is basically what I recommended, at least the heavier usage clients should be moved into their own instance. That is just not what accounting wants to hear 😉
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply