CXPACKET Wait-type Observed Frequently
CXPACKET wait is another bottleneck TIBCO observed. This wait-type occurs when SQL server spawns a single query on multiple worker threads.
The default maximum degree of parallelism (MAXDOP) is set to 0 which requires infinite worker threads to execute the query. Microsoft recommends that you optimize the queries, however because most of these are product queries you may encounter limitations when optimizing some of them.
TIBCO recommends that you set the MAXDOP to 10. In our environment there were 32 physical CPUs and 40 CPUs with hyper-threading.
TIBCO also recommends setting the MAXDOP to 25% of the total number of available CPUs. TIBCO decided that the MAXDOP set to 10 will allow the query to spawn across 25% of the total CPUs leaving the remaining 75% available for other threads. If you allow one query to spawn across 100% of the CPUs it may cause CPU contention.