Server Configuration Change Results

Last Post 14 Apr 2013 04:53 AM by gunneyk. 4 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages Resolved
rerichards
New Member
New Member

--
03 Apr 2013 01:44 PM
I recently changed the Max Degree of Parallelism and Cost Threshold of Parallelism on a server and was attempting to gauge the results, whether positive or negative, and I am unsure as to the results, if they are positive or negative.

In order to evaluate the change I have primarily been using sys.dm_os_wait_stats, looking primarily at CXPACKET waits. Not sure if that is the best way to monitor a change to Max Degree of Parallelism and Cost Threshold of Parallelism, but that is what I have focused upon.

This box has 8 processors and 32 GB RAM with Max Server Memory set at 25 GB. We are running SQL Server 2008 R2 Enterprise.

The original configuration run values were:
Max Degree of Parallelism = 2
Cost Threshold of Parallelism = 5

The new configuration run values are:
Max Degree of Parallelism = 4
Cost Threshold of Parallelism = 40

The resulting sampling of CXPACKET wait data from sys.dm_os_wait_stats is the following:

Original Configuration:
Waiting_Tasks_Count: 25520335
Wait_Time_ms: 85087860
Signal_Wait_Time_ms: 12214796
SignalWaitsPct = 14.35%

New Configuration:
Waiting_Tasks_Count: 28454233
Wait_Time_ms: 103599017
Signal_Wait_Time_ms: 9020827
SignalWaitsPct = 8.7%

So, based off the SignalWaitsPct, if appears the change (the New Configuration) is positive, but the New Configuration Waiting_Tasks_Count and Wait_Time_ms are clearly higher in comparison to the Original Configuration.

Any ideas on how to interpret these metrics, or are there other counters or metrics I should use to make a better evaluation?
rm
New Member
New Member

--
04 Apr 2013 04:43 AM
We change maxdop based on type and number of processors on the server but not change CTP, then use maxdop hint in queries or set resource governor if necessary.
rerichards
New Member
New Member

--
04 Apr 2013 06:04 AM
Thanks rm, but that does not address the question I asked.
rm
New Member
New Member

--
04 Apr 2013 08:38 AM
You should benchmark specific queries to find out if changes are positive or not. Most likely positive for certain queries but not for others per our experience, that's why we don't make such change.
gunneyk
New Member
New Member

--
14 Apr 2013 04:53 AM
First off you cant really tell anything meaningful from looking at the waitstat results from a single snapshot. You need to take two and do a delta so that you know exactly what happened in a specific period of time. If comparing to another then the load and timeframe should be the same as well. Otherwise it's apples to oranges. CXPacket waits are tough to troubleshoot to begin with but I agree with rm in that you should look to see how it affects your query execution as well. Are the parallel queries running better now than before? My guess is that with 8 cores and only 32GB total running Enterprise you are probably memory constrained more than DOP issues.
You are not authorized to post a reply.

Acceptable Use Policy