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). http://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.
- A Windows processor group (K-Group) is limited to 64 CPUs
- The internal, SQL Server, affinity mask(s) align to K-Groups as 64 bit integer values
- The value of 64 comes up over and over in lots of documentation, including SQL Server Books Online. http://technet.microsoft.com/en-us/library/ms189094(v=sql.110).aspx
- Recommendations for MAX DOP abound, such as: http://support.microsoft.com/kb/2806535
- sp_configure ‘max degree of parallelism’ set to 0 means 64
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