How many logical processors does SQL Server 2008 R2 Enterprise Edition support

I hope it is not too boring to bring up an article in regards to licensing. But this article by our team member Cameron on this blog site caused a quite bit of discussion. The key sentence in this article the discussions circled around this statement:

SQL Server 2008 R2 Enterprise Edition allows a maximum of 8 sockets/processors and up to 256 Logical Processors

A statement we made talking to our developers and looking through the SQL Server 2008 R2 coding. However a statement which is hard or nearly impossible to confirm with what is published as licensing guidelines or maximum capacity capabilities between the different SKUs of SQL Server. Searching through the internet also gives completely different answers. Some Blogs and articles are talking about a restriction of SQL Server 2008 R2 Enterprise Edition to a maximum of 64 logical processors. Other articles only talk about a limitation to a maximum of 8 processor/sockets with Enterprise edition without going into the details. At the end the publications in-official or official leave enough doubt on SQL Server 2008 R2 Enterprise Edition not being able to address up to 64 Logical Processor. With that restriction it would not be suitable for the existing 8-socket hardware and the upcoming generation of 4-socket hardware. After I got approached again today in regards to Cameron’s statement in our blog article I decided to put the whole thing to rest with a test that shows and demonstrates that, in opposite to SQL Server 2008 Enterprise Edition, SQL Server 2008 R2 Enterprise Edition can leverage more than 64 logical processors.

Hardware used:

Last week Intel launched the newest release of the Intel Xeon E7 processor family. This new release can have up to 10 cores per processor (socket). Using Hyperthreading one processor (socket) will show up as 20 logical processors towards the Windows Operating system and its applications. Hence taking a server with 4 of these new processors is resulting in 80 logical CPUs.

SQL Server Edition used:

According to the result of this statement:

select @@version;

I used this SQL Server release:

Microsoft SQL Server 2008 R2 (RTM) – 10.50.1600.1 (X64) Apr 2 2010 15:48:46

Copyright (c) Microsoft Corporation

Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600: )

Test executed:

One method to see how many CPUs are supported by SQL Server is to count the number of schedulers which are in the state ‘VISIBLE ONLINE’. SQL Server will always start up with a scheduler for ALL the logical processors SQL Server recognizes at startup on the server hardware. But it might expose only the schedulers which are configured according to the processor affinity mask. The other schedulers which are not active are in the state of ‘VISIBLE OFFLINE’. We also know that SQL Server will create one scheduler per logical processor usable for normal user connections. As a result we need to have 80 schedulers of in the status ‘VISIBLE ONLINE’ for the case that SQL Server 2008 R2 Enterprise Edition does support more than 64 logical processors on the hardware we used. The statement

select COUNT(*) from sys.dm_os_schedulers where status = ‘VISIBLE ONLINE’

would give an excellent count of the number of logical processors which can be used by SQL Server on a given hardware.

Executing the statement above immediately after the installation of SQL Server (default configuration), resulted in the number of 80 schedulers in the state of ‘VISIBLE ONLINE’.

This proves that Enterprise Edition of SQL Server 2008 R2 does address more than 64 logical processors. Fact is that it has the same limitations in regards to logical processors as the Datacenter Edition – in other words both SQL 2008 R2 x64 Enterprise Edition and SQL 2008 R2 Data Center Edition both support a maximum of 256 Logical Processors. 

The difference in this area is that Data Center Edition supports up to 64 “Processors” (each having “n” number of cores and logical processors) and Enterprise Edition supports 8 “Processors”.

I hope this clarifies the questions and assures that SAP customers who buy new server hardware which will exceed the 64 Logical processor limits can fully leverage the hardware capabilities of the new server with the SQL Server SKU which is supported with SAP.