SQL Server 2008 uses half the CPU’s
I recently got my hands on a couple of 4 socket servers with Intel E7-4870’s (10 cores per cpu) and with hyper threading enabled that gave me 80 logical CPU’s.
The server has Windows 2008 R2 SP1 along with SQL 2008 (Currently we can not deploy SQL 2008 R2 for the application being hosted).
When SQL Server started I noticed only 2 NUMA nodes were configured and 40 logical cores where there should have been 4 NUMA nodes and 80 logical cores (see below).
The problem is caused by that fact that although SQL 2008 Enterprise supports 8 physical processors (http://msdn.microsoft.com/en-us/library/ms143760(v=sql.105).aspx) it has a limit of 64 logical cores.
With the 64 logical core limit in mind you might ask why we can’t see 3 nodes or 60 cpu’s. This is because to address the 64 logical core limit the operating system introduces processor groups http://msdn.microsoft.com/en-us/library/dd405503%28VS.85%29.aspx.
In my case we have 2 processor groups with 2 processors in each group. If SQL server were to include the 2nd processor group it then exceeds the processor limit of 64 hence SQL only uses one processor group = 40 logical cpu’s/2 physical processors.
At this point there are 2 ways to progress. SQL 2008 R2 increases the logical core limit to 256 so upgrading would solve the issue allowing access to all 80 logical CPU’s but I can not use SQL 2008 R2 at the moment so i needed an alternative that maximises the CPU utilisation.
My answer was to access all the physical CPU’s by disabling hyper threading in the bios to bring us down to 40 physical cpu’s. Once done you can see that there are now 4 numa nodes shown in the logs and 40 cpu’s..
This is the most optimal configuration when running SQL 2008 but may not be the most optimal usage of the server. I will revisit upgrading the version of SQL later in the year and if we do upgrade we will benchmark whether having hyper threading enabled is beneficial for our workload or not.
If you want a good summary of Processor Groups, Numa, Cores etc then there is an excellent post on the Running SAP on SQL Server blog. The same blog also has a good post on the number of logical processors supported.