Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 3 down vote favorite

I recently took over a dual instance virtualized SQL Server with 8 x vCPUs and 8 x physical cores. This is the only VM on the host.

MAXDOP is at the default setting.

The server has 100 high transaction vendor specific databases with some very nasty reporting queries.

CXPacket waits are at the top of the waits list with 63%.

Signal Waits are averaging 15.50%.

In PerfMon the Processor Queue Length rarely breaches 1.

Does this justify setting MAXDOP to 4 or 6?

share|improve this question
2  
The purpose of lowering parallelism would be to increase concurrency. If you are getting CPU queuing then concurrency wouldn't appear to be a problem. I see no reason to justify lowering Max DOP. I would want to get rid of multi-instances though. – Robert L Davis Oct 19 '12 at 15:01
3  
I wouldn't worry too much about total waits. The funny thing about ranked lists is something has to always be the top item. I would look at waits of active queries and find queries that are experiencing high CXPacket waits and see if it can be tuned to run more optimally. – Robert L Davis Oct 19 '12 at 15:16
I agree with all of the above however that does not mean that MAXDOP cannot be an issue on either a virtual machine or a machine with hyper threading. Testing a lower MAXDOP could be one of many things you test while trying to optimize the overall query. I however doubt it will resolve your issue. – Zane Oct 19 '12 at 15:36
For completeness, which versions of SQL Server are in play? How much memory? Which version of VMWare? Are there > 1 NUMA nodes exposed to the VM? – Jon Seigel Oct 19 '12 at 17:24
MAXDOP to reduce CXPACKET waits is like turning up the radio when your car starts making a loud grinding noise - the symptom goes away but not the root cause. – JNK♦ Oct 19 '12 at 17:43
show 2 more comments

1 Answer

up vote 1 down vote

I would investigate the CXPacket issues first - see what's causing those. Can you identify a selection of queries that cause those wait types? Can you then see what is causing the problem through the actual execution plan? You're looking at gather streams steps, and looking for uneven loads coming in. Try to identify the source of those - a likely suspect is out-of-date / not helpful index statistics.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.