Home > Configuration, Numa, SQL Server > SQL Server 2008 uses half the CPU’s

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).

clip_image001

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..

clip_image002

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.

About these ads
Categories: Configuration, Numa, SQL Server
  1. No comments yet.
  1. No trackbacks yet.

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

Follow

Get every new post delivered to your Inbox.

Join 73 other followers

%d bloggers like this: