Low CPU Utilization but High Signal Waits

Question

I have a server with 16 CPUs that is configured with a max degree of parallelism of 8 and a max worker threads setting of zero.

For a given hour, my signal waits were 20% yet my OS CPU utilization during that time never went over 25%. Can someone explain why my signal waits were so high?

My vendor has a best in class scoring system that expects us to be at 10% or less signal waits, or we get dinged. How can I go about fixing this (without adding additional CPUs)?

• We do not have more than 8 CPUs per NUMA node, so Trace Flag 8048 does not apply.
• The largest instance waits are CXPACKET (70%),then PREEMPTIVE_OS_PIPEOPS (20%)
• cost threshold for parallelism is set to 50. Should I raise it? To what?
• This is a physical machine (not a VM), dedicated to SQL Server.
• I am using a monitoring tool to identify the most frequently run queries and procedures. Do I want to look at high CPU, high I/O, or high duration? Normally our app is I/O intensive so I tune high I/O. But since the issue is signal waits, do I need to look at high CPU?
• I was hoping to avoid Max Vernon's recommendation to lower MAXDOP to 4, because the app does some warehouse style queries that need the extra threads.

Answer 1

_{Community Wiki answer generated from comments on the question by Aaron.}

Unless you are having performance issues, the high percentage of CXPACKET waits may only be an indicator that a large portion of the queries are going parallel and not actually a problem.

• What is the CXPACKET wait type and how do you reduce it?

High CPU could be a indicator, but from what you have explained I would say look at queries with long durations but low CPU to start with. The CXPACKET wait is sometimes associated with a query waiting for all of the threads to finish before it can merge the results (data skew).

If you are able to modify the queries and procedures, you could set a higher MAXDOP for the warehouse tasks that need it, and set the global MAXDOP lower. However, I would only do that as a last resort. You only really want to make those types of explicit hints when you've exhausted all of the possibilities or you can't make changes to the code, queries or database schemas.