SQL Server on Windows 7 / Windows 2008 R2 with more than 64 CPUs

The release of the Windows 7/Windows 2008 R2 will support more than 64 CPUs.   It is also documented that the SQL Server 10.5 (Kilimanjaro - https://news.cnet.com/8301-13860_3-10236936-56.html) will extend the SQL Server Engine to support more than 64 CPUs.

Question: What if I am running a version of SQL Server that is not greater than 64 CPU aware?

Windows 7 introduces processor groups of up to 64 CPUs per group.   It is not common, but it is possible, to have configurations with unbalanced group CPU counts.   For example, on a machine with 96 CPUs you could have two groups 1 == 64 CPUS and 2 == 32 CPUs.  If you have SQL Server 2008 Enterprise installed it can use up to 64 CPUs.   You don’t want it associated with group 2 and only 32 CPUs.

Windows provides capability to assign a service to a preferred node using SC.exe.  You may need to use SC.exe to establish a preferred node for the SQL Server based service.

//To reset a preferred node

C:>sc preferrednode spooler -1

//To set a preferred node

C:>sc preferrednode spooler 2

//To query a preferred node

C:>sc qpreferrednode spooler

Group Reference: https://download.microsoft.com/download/a/d/f/adf1347d-08dc-41a4-9084-623b1194d4b2/MoreThan64proc.docx

Group, Process, and Thread Affinity

In earlier versions of Windows, a process or thread could specify an affinity for a particular processor, so that the thread or process was guaranteed to run on that processor. Windows 7 expands this notion of affinity to apply to groups and to the processors in a group.

Windows 7 uses the following defaults for affinity:

· Windows 7 initially assigns each process to a single group in a round-robin manner across the groups in the system. A process starts its execution assigned to exactly one group.

· The first thread of a process initially runs in the group to which Windows assigns the process. However, an application can override this default as described in “Setting Process Affinity” later in this paper.

· Each newly created thread is by default assigned to the same group as the thread that created it. However, at thread creation, an application can specify the group to which the thread is assigned.

· Only the system process is assigned a multigroup affinity at startup time. All other processes must explicitly assign threads to a different group to use the full set of processors in the system.

Over time, a process can expand to contain threads that are running on all groups in a machine, but a single thread can never be assigned to more than one group at any time. However, a thread can change the group to which it is assigned.

Bob Dorr – Principle SQL Server Escalation Engineer