Wednesday, March 25, 2009

MAXDOP - Maximum Degree of Parallelism

Symptoms : CXPACKET and PAGEIOLATCH_SH wait types in Activity Monitor.

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: