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.
- 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. https://technet.microsoft.com/en-us/library/ms189094(v=sql.110).aspx
- Recommendations for MAX DOP abound, such as: https://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
Comments
Anonymous
November 10, 2014
In SQL Server 2005 there was a hard-coded CPU maximum of 64 cores.Anonymous
October 19, 2016
Does It mean we have to specify explicitly in statement MAXDOP>64 to get use of CPUs>64Anonymous
September 25, 2017
新品同様に磨かれたカデナで大満足です。小さなパーツの一つ一つまで心の行き届いたメンテナンスでスタッフの皆さんに感謝。信頼できるショップです。次の機会にも是非利用したいお勧めのショップです。ブランド時計コピー http://www.kopi78.com