Bhuvnesh (1/28/2013)

---

Another thing which we need to consider is ,look for queries that run under parallelism and test them manually using different levels of DOP using the OPTION(MAXDOP n) query hint to see if reducing parallelism actually improves or harms performance.

Tune the query before tuining the server

sqldba_newbie (1/28/2013)

---

You still didnt answer my question.

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.

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

sqldba_newbie (1/29/2013)

---

If there are 10 processes running wouldnt they use 10 different core's if MAXDOP is set to 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?