SQL Server Performance – Queries Running Parallel with Default MAXDOP

maxdopperformancequery-performancesql serversql-server-2012

I need to understand below behavior of sql server queries

System has 4 CPUs with hyper thread ratio of 20 making 80 logical processors count on my sql server instance.

Currently MAXDOP is 0 and ctop is 5

This is a read only instance acting as stand by for LS where all reporting is done.

Lot of queries coming in seems to show running with degree of parallelism 64.

Questions:
.1. why degree of parallelism is not 80 when no of logical processors is 80?

  1. Is my understanding correct that if there are 6 slow queries running over an hour all showing degree of parallelism 64 would be expected to slow as they might be hitting same threads and thus showing as cx packet waits. Hence I believe changing MAXDOP to say 16 or 8 by testing should give them their own threads to complete?

Thanks

Best Answer

The simple answer is that 64 is the DOP limit when zero is specified:

To enable the server to determine the maximum degree of parallelism, set this option to 0, the default value. Setting maximum degree of parallelism to 0 allows SQL Server to use all the available processors up to 64 processors.

A single query can use more than 64 cores if you set that specific number (i.e. not zero):

Set the value to a number from 1 to 32,767 to specify the maximum number of processor cores that can be used by a single query execution

See also SQL Server MAX DOP Beyond 64 – Is That Possible?

As for how SQL Server will allocate threads, this blog post is the closest I've seen to official documentation:

SQL Server Parallel Query Placement Decision Logic

Related Question