Queries running parallel with default maxdop

Question

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?

2. 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

• David Browne - Microsoft: Note that this is a scenario where you might want to turn off Hyperthreading to reduce the logical processors from 80 to 40. Or to set the CPU affinity to use 16 cores on each NUMA node.
• BeginnerDBA: @DavidBrowne-Microsoft: Thanks for your comment. But i did'nt understand why would one want to reduce the number of logical processors because additional 40 always sound good if we have these big select queries running on db 20 TB+ [querying partition]. Also setting CPU affinity will have what impact or assistance here. I am just trying to understand before going with any change
• David Browne - Microsoft: Hyperthreaded cores aren't real cores. It's a way to schedule two threads on a single core. So turning off hyperthreading has only a marginal impact on performance, one way or the other. But it would allow a single query to use all 40 of your cores, where today a single query can only use 32 cores.
• BeginnerDBA: @DavidBrowne-Microsoft: Thank you sir. Does that make any difference if that server has 4 numa nodes ?

Answer 1

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