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

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

  • Comments 4

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.

Leave a Comment
  • Please add 1 and 8 and type the answer here:
  • Post
  • Hello Juergen,

    I have one question about the number of supported processors. In the sap note 1237682 - Configuration Parameters for SQL Server 2008 - the maxdop advise is to set this option on sap systems to 1. But the note say also, that when the number of active connections are smaller than the amount of processors on the database server this rule can vary.  We have now this sitiuation for one of our sap systems  (normal SAP ERP). Is there commendation from Microsoft which value these parameter should look like (2 ,3,4,...) ? Does the compression feature (page) also profit from a increasement of that parameter ?

    Kind regards

    Elmar Fecker

  • Hi Elmar,

    in the situation you describe I first would set maxdop to 2 and see whether there is some gain or better utilization of the available CPU resources. Please be aware that SQL Server decides on parallel execution of queries only when it is estimating that queries take a few seconds (default = 5sec). Hence only a small # of queries might be gaining by an increased maxdop. If you feel that there are more untapped CPU resources, you could go to a maxdop=4.

    Once the data is compressed, Database Compression will not benefit of an increased maxdop. Other activities which might benefit of an increased maxdop are:

    - Index build/rebuild to a degree

    - Checkdb/checktable

    Hope this helps

  • What edition of the OS were you using - our Windows 2008 R2 Enterprise Edition sees only 64 of the 80 Processors?

  • Hi,

    I just want to know the query to see number of cpus used by sql server surrently.

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

    will give tne number of cpu configured for use by sql server right?

    select cpu_count from sys.dm_os_sys_info will give the total cpus available in the system right?

Page 1 of 1 (4 items)