SQL server 2005 can’t leverage more than 64 processors. Can it?

I was working on a very interesting scenario, where my customer had 4 X 10 core processors with hyperthreading enabled.  In short, they had 80 processors on windows 2008 R2 machine and SQL server was able to see only 40 processors.

Environment :

msinfo32
==========
OS Name Microsoft Windows Server 2008 R2 Enterprise
Version 6.1.7601 Service Pack 1 Build 7601
System Type x64-based PC
Processor Intel(R) Xeon(R) CPU E7- 4860 @ 2.27GHz, 2266 Mhz, 10 Core(s), 20 Logical Processor(s)
Processor Intel(R) Xeon(R) CPU E7- 4860 @ 2.27GHz, 2266 Mhz, 10 Core(s), 20 Logical Processor(s)
Processor Intel(R) Xeon(R) CPU E7- 4860 @ 2.27GHz, 2266 Mhz, 10 Core(s), 20 Logical Processor(s)
Processor Intel(R) Xeon(R) CPU E7- 4860 @ 2.27GHz, 2266 Mhz, 10 Core(s), 20 Logical Processor(s)
Installed Physical Memory (RAM) 128 GB
Total Physical Memory 128 GB
Available Physical Memory 120 GB
Total Virtual Memory 256 GB
Available Virtual Memory 247 GB
Page File Space 128 GB
Page File C:pagefile.s

On this we machine we had total 40 cores and with hyperthreading it made 80 logical processors. To check about hyperthreading, please check this blog.

SQL server
=========
Microsoft SQL Server 2005 – 9.00.5000.00 (X64) Copyright (c) 1988-2005 Microsoft Corporation  Standard Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1)

Informational messages in the SQL errorlog:
2012-02-22 03:47:11.220 Server       Detected 40 CPUs. This is an informational message; no user action is required.
Multinode configuration: node 0: CPU mask: 0x000000fffff00000 Active CPU mask: 0x000000fffff00000. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required. 2012-02-22 03:47:11.550 Server
Multinode configuration: node 1: CPU mask: 0x00000000000fffff Active CPU mask: 0x00000000000fffff. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.

Ran query (output was obvious):-

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

Now, the requirement was – how to make SQL server 2005 aware of all the 80 processors. It’s a very interesting scenario. isn’t it?  If we see the no. of sockets (http://blogs.msdn.com/b/saponsqlserver/archive/2010/09/28/windows-2008-r2-groups-processors-sockets-cores-threads-numa-nodes-what-is-all-this.aspx) supported by SQL server standard edition are 4,  despite the number of cores :   –  http://msdn.microsoft.com/en-us/library/ms143760(v=sql.90).aspx. So, Ideally we should support all of them.

Started research on this issue, here are few interesting things I found:

Windows 2008 R2 and windows 7 onwards – to support more than 64 processors, we have a new concept added in called processor groups : – http://msdn.microsoft.com/en-us/library/dd405503%28VS.85%29.aspx.

To operate correctly on systems with more than 64 logical processors, the following kinds of applications might require modification:

  • Applications that manage, maintain, or display per-processor information for the entire system must be modified to support more than 64 logical processors. An example of such an application is Windows Task Manager, which displays the workload of each processor in the system.
  • Applications for which performance is critical and that can scale efficiently beyond 64 logical processors must be modified to run on such systems. For example, database applications might benefit from modifications.
  • If an application uses a DLL that has per-processor data structures, and the DLL has not been modified to support more than 64 logical processors, all threads in the application that call functions exported by the DLL must be assigned to the same group.

In short, to work with more than 64 processors, the applications have to be modified. Apparently. SQL server 2005 didn’t seem to support this group machenism of Windows 2008 R2. But , I hadn’t lost the hope yet . So, i started digging in further to find out more about the issues and solutions to resolve this issue. One very good article I came across was:
http://blogs.msdn.com/b/saponsqlserver/archive/2011/04/20/changes-in-affinity-settings-of-sql-server-2008-r2-to-support-gt-64-logical-processors.aspx.

In this article author had explained that we can change the processor affinity by using Alter server configuration command. Alas, it was for only applicable to SQL server 2008 R2. Checked for Alter server configuration command in SQL server 2005 and there was no such command(so, no help).

I was curious to know, how my hardware(Processor) looks like to the operating system and I wanted to know about the groups created by the OS. There is a very good utility named coreinfo.exe which can be downloaded from here :- http://technet.microsoft.com/en-us/sysinternals/cc835722.aspx.  When I ran this utility, I got the results(seletive output shown) :

Intel(R) Xeon(R) CPU E7- 4860 @ 2.27GHz
Intel64 Family 6 Model 47 Stepping 2, GenuineIntel
HTT * Hyperthreading enabled 
HYPERVISOR – Hypervisor is present
VMX * Supports Intel hardware-assisted virtualization
SVM – Supports AMD hardware-assisted virtualization
EM64T * Supports 64-bit mode

Logical Processor to Socket Map:

Socket 0:
********************——————–
—————————————-
Socket 1:
——————–********************
—————————————-
Socket 2:
—————————————-
********************——————–
Socket 3:
—————————————-
——————–********************

=> 4 sockets

Logical Processor to NUMA Node Map:

NUMA Node 0:
********************——————–
—————————————-
NUMA Node 1:
——————–********************
—————————————-
NUMA Node 2:
—————————————-
********************——————–
NUMA Node 3:
—————————————-
——————–********************
=> 4 NUMA nodes


Logical Processor to Group Map:

Group 0:
****************************************
—————————————-
Group 1:
—————————————-
****************************************

It gave me the clarity that, we have got 2 groups 40 processor each( * refer to logical processor and – refer to not part of the logical group ). As per the article : – http://blogs.msdn.com/b/sqlserverfaq/archive/2010/06/28/how-to-set-soft-numa-for-sql-server-2008-r2.aspx OS will always try to make group with maximum no. of CPU’s e.g.  a system with 128 logical processors would have two processor groups 64 processor each, not four groups with 32 logical processors in each group.  In our scenario, it made 40 processors in each group.

After further research on this part, I got to know that we can customize the CPU groups. Here is the article to do that: – http://msdn.microsoft.com/en-us/library/windows/hardware/ff542298(v=vs.85).aspx.

Note:- Please take a backup of Boot.ini file before running any of the below commands

To understand the behaviour, I ran the command in command prompt:

1. bcdedit.exe /set groupsize 16
2. Restarted the machine and msinfo32 looked like:

OS Name Microsoft Windows Server 2008 R2 Enterprise
Version 6.1.7601 Service Pack 1 Build 7601
System Type x64-based PC
Processor Intel(R) Xeon(R) CPU E7- 4860 @ 2.27GHz, 2266 Mhz, 3 Core(s), 6 Logical Processor(s)
Processor Intel(R) Xeon(R) CPU E7- 4860 @ 2.27GHz, 2266 Mhz, 2 Core(s), 4 Logical Processor(s)
Processor Intel(R) Xeon(R) CPU E7- 4860 @ 2.27GHz, 2266 Mhz, 3 Core(s), 6 Logical Processor(s)
Processor Intel(R) Xeon(R) CPU E7- 4860 @ 2.27GHz, 2266 Mhz, 3 Core(s), 6 Logical Processor(s)
Processor Intel(R) Xeon(R) CPU E7- 4860 @ 2.27GHz, 2266 Mhz, 2 Core(s), 4 Logical Processor(s)
Processor Intel(R) Xeon(R) CPU E7- 4860 @ 2.27GHz, 2266 Mhz, 3 Core(s), 6 Logical Processor(s)
Processor Intel(R) Xeon(R) CPU E7- 4860 @ 2.27GHz, 2266 Mhz, 3 Core(s), 6 Logical Processor(s)
Processor Intel(R) Xeon(R) CPU E7- 4860 @ 2.27GHz, 2266 Mhz, 1 Core(s), 2 Logical Processor(s)
BIOS Version/Date HP P65, 3/1/2011
SMBIOS Version 2.7
Windows Directory C:Windows
System Directory C:Windowssystem32
Boot Device DeviceHarddiskVolume2
Locale United States
Hardware Abstraction Layer Version = “6.1.7601.17514”
Installed Physical Memory (RAM) 128 GB
Total Physical Memory 128 GB
Available Physical Memory 121 GB
Total Virtual Memory 256 GB
Available Virtual Memory 248 GB
Page File Space 128 GB
Page File C:pagefile.sys

SQL errorlog
=========
SQL detected only :-
16 CPUs.

Coreinfo.exe output
==============

Logical Processor to NUMA Node Map:

NUMA Node 0:
****************
————————

NUMA Node 1:

—————-
****************
——–
NUMA Node 2:
————————

=> 3 NUMA nodes

Logical Processor to Group Map:

Group 0:
****************
————————

Group 1:
—————-
****************
—————–

Group 2:
——————————–
********

=>3 Groups

It gave some clarity whatever was there in group 0(count the no. of stars , are 16) was being assigned to the SQL server. Previously, we had 40 processors in group 0 and SQL was able to use 40 and now it’s 16 and SQL is using 16.

Tried to bcdedit.exe /set groupsize 64 , but no help – it used to again come to 40 CPUs in each group – not sure of the reason.

* If on the same set of hardware if we install Windows 2008 (not R2), it will only be able to see 64 processors. Also, please be aware that you may see some weird scenarios like only 4 schedulers are online and remaining 60 are offline. To workaround those issues, you will have to tweak affinity mask and check.

In conclusion :- It’s limitation of SQL server 2005 that it can’t use all the 80 processors. But, still we can make SQL server to use atleast 64 processors. Here is how we can do that:

1. Restart the machine
2. go to boot options by pressing F2
3. go to performance ->multi core support
4. select the 8 cores instead of all 10.
5. Restart the machine

Note:- Please do proper testing before doing this on production server.

In summary, this may be a known behaviour on SQL server 2005 and SQL server 2008. From SQL server 2008 R2 onwards, it will be able to make use of all the processors.

Here are few good resources about NUMA,processors:

http://msdn.microsoft.com/en-us/library/ms345403.aspx
http://blogs.msdn.com/b/psssql/archive/2008/01/24/how-it-works-sql-server-2005-numa-basics.aspx
http://blogs.msdn.com/b/sqlserverfaq/archive/2010/06/28/how-to-set-soft-numa-for-sql-server-2008-r2.aspx
http://blogs.msdn.com/b/slavao/archive/2005/08/18/453354.aspx
http://support.microsoft.com/kb/2510206
http://blogs.msdn.com/b/saponsqlserver/archive/2011/04/20/changes-in-affinity-settings-of-sql-server-2008-r2-to-support-gt-64-logical-processors.aspx
http://msdn.microsoft.com/en-us/library/dd405503%28VS.85%29.aspx
http://blogs.msdn.com/b/saponsqlserver/archive/2011/10/08/uneven-windows-processor-groups.aspx
http://msdn.microsoft.com/en-us/library/ff795582(v=sql.100).aspx
http://msdn.microsoft.com/en-us/library/windows/hardware/ff542298(v=vs.85).aspx

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s