Solution : Adjust MAXDOP (the Maximum Degree of Parallelism) at a server level (see below) or use the OPTION (MAXDOP) query hint.
Reason : CXPACKET means processors are waiting for each other to finish tasks that are running in parallel. Setting MAXDOP to 3 on a 4 processor box means parallel processes cannot consume all resources in this way i.e. one processor is left to serve other queries.
Links :
Sql-server-Pro : Max degree of parallelism - When to change itJonathan Kehayias : Tuning the cost threshold of parellism
From the comments on Jonathan's post - Paul White’s suggestions for OLTP scenario...
Something that can work well in many environments, assuming a primarily OLTP-type workload:
1. Server-wide setting to MAXDOP = 1
2. Cost threshold set to zero
3. Use the MAXDOP = N query hint on code that benefits from parallelism (even if it's just MAXDOP 2 to allow bitmap operators).
No comments:
Post a Comment