GilaMonster (1/29/2013)
If you want all queries to only use one processor, why not pull the other 23 out? That's essentially what you've done by setting maxdop to 1. It is NOT a good thing to do.
If there are 10 processes running wouldnt they use 10 different core's if MAXDOP is set to 1?
Of course you won't see any CXPacket waits with maxdop at 1. Maxdop of 1 means never parallel.
Maxdop should probably be changed from the default, but not to 1. See the last section here: https://www.simple-talk.com/sql/database-administration/gail-shaws-sql-server-howlers/
CXPacket waits are not an indication of poor performance. They're an indication that queries are running in parallel. That is all. If you have lots and lots and lots of CXPacket, then, to reduce them and improve performance, you need to look for the other waits. In any query that's got huge CXPacket waits, there will be one or more threads that have some other wait type. That's the wait type that you need to investigate and resolve.
Look for the other wait types
Optimiser your queries
Increase cost threshold for parallelism
Set maxdop back to something > 1
I do see heavy waits on PAGEIOLATCH_SH. Do you have query to find total wait on waitype PAGEIOLATCH_SH for a particular spid?