Page History
...
- Increase the Cost threshold for parallelism server configuration option until the waits are under control.
- Use the ‘OPTION (MAXDOP 1)’ query hint to prevent parallelism when you identify specific queries that cause the CXPACKET waits.
- Set the Max degree of parallelism server configuration option to half the number of physical processors on the server up to a max of eight. This configuration prevents a query from monopolizing all of the processors at the detriment of other activity on the server. As a last resort, set the Max degree of parallelism server configuration option to 1 to prevent any query parallelism.
CXPACKET waits for non-OLTP servers
CXPACKET waits for non-OLTP servers (Reporting, OLAP, Hybrid-OLTP, etc.) are indicators that parallelism takes place.
...
- Queries may need to be tuned
- Indexes may need to be added and/or correctly tuned for the workload
- Index reorganization/rebuilding may be needed
- Statistics may be out of date
Configure your server options for parallelism
Max degree of parallelism
Leave the Max degree of parallelism server configuration option at zero. Note that this configuration may be acceptable for non-OTPL Servers. You can also consider setting the Max degree of parallelism server configuration option at half the number of physical processors on the server up to a max of eight.
Cost threshold for parallelism
Set the Cost threshold for parallelism server configuration option at the default setting of 5.
...