SQL Server MAX DOP Beyond 64 – Is That Possible?

I recently posted a blog outlining how the partitions of a table can be used in the calculation for the achievable max degree of parallelism (MAX DOP). https://blogs.msdn.com/b/psssql/archive/2014/09/04/a-partitioned-table-may-limit-the-runtime-max-dop-of-create-alter-index.aspx 

Discussing this with various peers I uncovered a perception that SQL Server was always limited to a max of 64 CPUs, even if the machine had more (128, 160, …)   This is not the case, instead the perception is semantic driven and once you realize how to leverage it maintenance operations can take advantage of more than 64 CPUs.

It is not hard to understand how the perception started or continues to propagate itself.

SQL Server Books Online states: “Setting maximum degree of parallelism to 0 allows SQL Server to use all the available processors up to 64 processors. “ and that is where most of us quit reading and assume the MAX DOP for SQL Server is limited to 64.

Instead if you read a bit further: “If a value greater than the number of available processors is specified, the actual number of available processors is used.”

Simply stated if you tell SQL Server to use more than 64 CPUs SQL Server will attempt to do just that.

Bob Dorr - Principal SQL Server Escalation Engineer