October 30, 2008 at 7:43 am
Hi All,
Microsoft recommended we set our maxdop to 4. We are working with 8 processors in an oltp environment. The setup was done about 6 weeks ago and i am seeing some blocking, and high CPU times. After analyzing the DMV's it appears the CXPACKET is causing nearly 40% of our waiting. I read a lot of articles that suggest i should set the maxdop to 2.
My questions are as follows:
1. Is this safe to do on a live environment
2. How do i know if this is the right choice
3. What adverse affects could I possibly come across
Thanks everybody in advance for any help.
October 30, 2008 at 8:24 am
Is it 8 processors or 4 processors hyperthreading? If it's the latter, I'd go with 4, the former, 7 and maybe set affinity.
Also, since this is an OLTP system, I'd adjust the parallelism threshold up,say to 20 to start.
EDIT NOTE:
I wrote duplexing instead of hyperthreading... I'm that stupid.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
October 30, 2008 at 11:38 am
Its 8 processors. We have the cost threshold of parallelism set to the default of 5 and the maxdop set to 4. Like i said we do experience some blocking so i was thinking of passing the maxdop query hint of one to the main culprit. Can the suggested server side settings be done without bringing things down?
Also if you happen to know, what qualifies a query for parallelism?
Thanks again.
October 30, 2008 at 11:54 am
Is this 8 proc cores or 8 logical procs hyperthreading, if they are logical procs you can turn this off in the bios. I have seen SQL handle logical procs inefficiently. The degree of parallelism is dynamic, you can change it on the fly without restarting SQL, unless you see drastic detrimental issues when testing you can scale through the maxdop setting and see how the box performs, run perfmon or simply keep an eye on the stats in DMV, or install the 2005 performance dashboard and keep refreshing as you observe. Once you think you have come up with the correct setting do a perfmon dump for a day and compare it to one you did before you did the change, the proof should be in the pudding.
Andrew
October 30, 2008 at 11:56 am
If you can determine that it is a finite number of procs\sql calls that causes the waits, you could also set the maxdop option in the code, maybe the rest of the code is happy with the maxdop set at the server level.
Andrew
October 30, 2008 at 11:58 am
I know you can change the threshold without taking the server offline. Based on the BOL, I'm pretty sure you can change the max degree of parallelism in the same manner. And yes, based on the BOL again, you could change the affinity as well, dynamically.
Parallelism is set in two places. The optimizer will determine, based on cost, the threshold and the max DOP, whether or not a parallel execution plan will be built. When it does build one, it builds a non-parallel plan too. Then, the data engine determines whether or not it will use the parallel plan based on workload and availability of the processors. So it may choose to use the parallel plan or not. Raising the cost threshold means that fewer parallel plans are even created in the first place, let alone used. More often than not, in my experience, parallelism in OLTP systems creates unnecessary and unwanted overhead. Opinions vary.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
October 30, 2008 at 12:05 pm
Grant,
There sure is a number of ways to skin the old cat, just takes time, effort and feel to hopefully get there in the end:)
Andrew
October 30, 2008 at 12:35 pm
Guys I just wanted to thank you again for all of your help. I have some work to do and with your input i think i will be able to get some better performance out of these boxes.
Thanks again,
Paul
September 28, 2010 at 9:29 am
Hello, here is a Microsoft article that talks about MAXDOP settings: http://support.microsoft.com/kb/329204
Set MAXDOP = n, where n is the number of physical processors in the box (for a box with eight processors or less).
October 6, 2010 at 6:05 am
paul.corujo (10/30/2008)
Microsoft recommended we set our maxdop to 4.
From whom was that advice obtained, and what were the circumstances? It's not (necessarily) bad advice, but I'm interested to hear more about the background.
We are working with 8 processors in an oltp environment. The setup was done about 6 weeks ago and i am seeing some blocking, and high CPU times.
What sort of blocking are you seeing precisely? By high CPU times, do you mean sustained utilization across all cores above 80%? Or are you seeing sustained utilization lower than that with occasional peaks to 80% or more? Do you see high waits on I/O?
After analyzing the DMV's it appears the CXPACKET is causing nearly 40% of our waiting. I read a lot of articles that suggest i should set the maxdop to 2.
Short CXPACKET waits are an expected consequence of parallelism and are rarely cause for concern. Long CXPACKET waits (or those rare situations where all parallel threads block at the same time) do merit investigation.
1. Is this safe to do on a live environment
Yes. Changing server MAXDOP from 4 to 2 is safe (as safe as any other real-time configuration change to a production system anyway).
2. How do i know if this is the right choice
Pure OLTP systems do not benefit from parallelism. However, there are precious few pure OLTP systems. Most systems have at least some queries that can make effective use of parallelism.
Some people prefer to set MAXDOP = 1 at the server level for mostly-OLTP systems, and then override that setting for particular queries using the MAXDOP(n) query hint.
The considerations are different on systems where parallelism is more generally useful (but these would not generally be described as OLTP).
Parallel query tuning is an advanced topic, so I normally recommend engaging an expert you trust to assist with the learning process.
3. What adverse affects could I possibly come across
Parallel plans that benefited from four threads may take longer to execute on two.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply