June 13, 2009 at 6:39 pm
Hi there the wait stats show the CTXPACKET when you use all CPU's -- well thats what i have seen in my case. What else can you show as analysis results for management that this is a good change and gains performance.
June 13, 2009 at 6:49 pm
Check out what you're seeing in dbcc sqlperf(waitstats) for SQL Server 2000 or sys.dm_os_wait_stats for SQL Server 2005. If you find that your CXPACKETS are more than 10% of the waits, then I believe MaxDop is for you.
Something else thats nice is that you don't have to reboot for the setting change to take affect. So maybe a demonstration on a test box would help.
When I started off looking into this, CXPACKET waits were 30% of the cause of waits. PAGEIOLATCH_ waits can also figure into this - they were (combined) 29%.
Since I made the change to MAXDOP 2 (on a system with 4 processors) and Cost of Threshold for Parallelism to 75, these waits went way down.
Now, CXPACKET waits account for 4.4% of the waits and the PAGEIOLATCH_ waits are 16%. Big ole difference!
Randy
June 14, 2009 at 4:37 pm
Cost of Threshold for Parallelism to 75 where did you set this in the GUI..and why 75 how to determine if need to change this?
So if you got 8 CPU and drop to say 4.
One thread starts uses 1-4 then another can use cpu 5-8 rather than one thread sprawn 1-8 CPU so in theory you could get more threads through and more throughput and processes
June 14, 2009 at 5:59 pm
TRACEY (6/14/2009)
Cost of Threshold for Parallelism to 75 where did you set this in the GUI..and why 75 how to determine if need to change this?
In SSMS, go to the explorer pane, right-click on the Server icon, select properties. In the Properties dialog, select the Advanced page, it's in there.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 14, 2009 at 6:26 pm
There's very few systems that are purely transactional: there's nearly always some sort of reporting being done on them. If you're lucky that's done in an out-of-hours batch window, but there's always some manager who wants realtime data (note that I didn't say "needs").
With multi-core processors now standard, quad-core with 6 and 8-core processors looming, every new system has plenty of cores to play with.
The MaxDOP and Cost Threshold parameters are great ways of tuning a system for different types of use. However, the default values for these, 0 and 5 respectively, are totally inappropriate for virtually all systems. I'm now using the following as default standards:
MaxDOP
25-50% of cores, up to a maximum of 8, decreasing as system becomes more "transactional"
Cost Threshold:
30-300, increasing as system becomes more transactional
NB. These aren't perfect, but they're something I use as a starting point.
June 15, 2009 at 10:41 am
Got the defaults here. Cost thershold for paralleisum 5 and max degree of paralleisum 0, max worker threads 0
Processors 8 CPU
processor affinity all ticked
i/o affinity all ticked.
When changing 8 to 4 do you just untick the 4 of them
Also the cost pralleisum setting this 750 - you see a performance increase in statements executed quicker? how did you benchmark.
Max Worker Threads - did you increase.
Cheers
June 15, 2009 at 10:59 am
TRACEY (6/15/2009)
Got the defaults here. Cost thershold for paralleisum 5 and max degree of paralleisum 0, max worker threads 0Processors 8 CPU
processor affinity all ticked
i/o affinity all ticked.
When changing 8 to 4 do you just untick the 4 of them
Also the cost pralleisum setting this 750 - you see a performance increase in statements executed quicker? how did you benchmark.
Max Worker Threads - did you increase.
Cheers
No, you don't want to tick off individual processors. That's setting affinity, a different critter entirely. Either use TSQL & the SET statements or go to the advanced tab. Change the Max Degree of Parallelism to the value you think appropriate for your system. Just above that is the Cost Threshold for Parallelism. You can change that value to something higher. 750 seems a bit extreme. Somewhere in the range of 25-50 seems common. I use 25.
I wouldn't mess with the threads unless you see an issue in that area.
"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 15, 2009 at 2:31 pm
Oh the CTXPACKET you fix by changing the COST OF PARALLELISM from 5 to say 25 and MAX
DEGREE OF PARAMLLEISM to 4 (for 4 cpu as right now it 0 and using 8).
Got it...............glad i asked.
June 16, 2009 at 6:14 am
They kind of work back and forth depending on what you want to accomplish. You can get rid of the CXPACKET waits by setting the Max Degree of Parallelism to 1. When you do that, though, that kind of ends parallelism in your system unless you use OPTION query hints. If you set Max Degree of Parallelism to something other than 1 or 0, then you're leaving the option open of more than one CPU being used in parallel execution.
Also, if you set Max Degree of Parallelism to something other than 0 or 1, you can steer how often the extra CPUs will be used by setting the Cost Threshold for Parallelism. The Cost Threshold specifies not to use parallelism unless a particular query plan exceeds the threshold you set. 5 will let pretty much anything but the simplest queries use parallelism. Set it to something higher (say, 50), and then adjust up or down until you get it where you want it. Mine is set at 80 because its compensating for some poor design. You may find that yours can be set at 25 more like Grant Fritchey's.
Randy
February 2, 2010 at 9:54 am
If anyone is still following this thread, a related question. If I want to use the MAXDOP query hint but set it dynamically, is there a way to tell how many processors are available? I don't see anything from sp_configure or sp_server_info. Would I need to look at the operating system using an xp?
I don't want to set it to zero but I also don't want to have to write something elaborate.
"Beliefs" get in the way of learning.
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply