Degree of Parallelism, Max Degree of Parallelism, Affinity Mask and Thread Allocation
Degree of Parallelism, Max Degree of Parallelism,
Affinity Mask and Thread Allocation
By : Kasim Wirama, MCDBA
You can view parallelism in your execution plan by viewing either graphically or textually. Optimizer decides between parallelism and serial plan on cost-based. Parallelism itself is determined by SQL Server advanced configuration setting and number of processor of SQL Server resides. One of advanced configuration item relating to parallelism is Max Degree of Parallelism and Affinity Mask.
If you don’t specify MAXDOP N query hints, by default max degree of parallelism is equal to number of processor that is allowed to give the thread to run corresponding parallelism operator. The number of allowed processor is determined through Affinity Mask in advanced configuration setting. If you specify MAXDOP N, it will used N DOP, but if you put MAXDOP 0, the max DOP will be equal to number of allowed processor in Affinity Mask.
DOP reflects number of thread that will execute on an operator. Even though you have max DOP more than allowed processor, it doesn’t mean that you can have all thread on the max DOP. It depends on the available thread which is specified in Max Worker Thread (found in advanced configuration setting). Max Worker Thread itself depends on number of processor and kinds of platform (32 bit or 64 bit). You can view how much query request takes number of thread through querying to Sys.dm_os_tasks. The information difference between sys.dm_os_tasks and parallelism in execution plan is that sys.dm_os_tasks gives information all threads in a query while parallelism in execution plan gives information about how much thread taken by each operators. Generally parallelism among operators could be executed at the same time, but exception applies here is that, if you found that there is blocking operator(s), the execution between operator before and after blocking operator is not done at the same time, so those operators can share resources such as memory and threads.