March 17, 2014 at 11:45 am
I'm evaluating OS Wait Stats on a server. CXPacket is by far the largest value I see. Unfortunately, I can't tune the queries that run on the server so I was thinking of changing the Cost Threshold value from 5 to something. My question is, is there a general formula or thought process behind the correct threshold value?
March 17, 2014 at 11:50 am
March 17, 2014 at 12:34 pm
Not per se. I have a general suggestion to bump it to 35. You can get more specific and query the plans in cache to get overall costs and then make a calculation as to what would be an average value for your system or a top value for your system, but usually, a ball park figure like this is good enough to get started.
"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 17, 2014 at 1:27 pm
Thank You
March 24, 2014 at 11:35 am
Just remember that CXPacket waits are often a side effect of inefficient queries or indexes leading to unnecessary CPU usage. If you run large DW queries, it might not be a problem at all. It only indicates that a part of a parallel query is finished and are waiting on the results of another parallel stream before it can complete.
It is only a system bottleneck if other processes also compete for CPU resources and you get high SOS_SCHEDULER_YIELD waits or signal waits.
If this is not the case increasing the 'cost threshold for parallelism' or reducing the 'max degree of parallelism' will actually make your queries even longer as you are not making Parallel CPU resources available to them.
Reducing CXPacket waits should never be a goal in itself, but rather investigating why your queries need to go 'Parallel'.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply