TheSQLGuru (10/17/2008)

---

At least 75% of my clients have multi-cpu servers with insufficient RAM or IO capabilities (or both!), and thus suffer from significant CXPACKET waits on largish queries. It is often optimal in general in situations like that to simply set MAXDOP at the server level to some fraction of the total number of CPUs (1/4 to 1/2 usually). In other cases you can specify the maxdop as an OPTION for an individual query.

NOTE: if you have hyperthreading enabled it gets MUCH worse. Consider disabling HT if you are seeing logs of CXPACKET waits first, then reevaluating and if necessary adjust MAXDOP.