16

After upgrading our SQL Server's hardware, we noticed in the Windows Task Manager that the SQL instance is only using half of the threads available to it:

Task Manager.

The server has the following hardware and software:

  • Windows 2008 R2 Enterprise 64bit SP1
  • Intel Xeon E7-4870 - 4 processors (40 cores, 80 threads)
  • Microsoft SQL Server 2012 Enterprise Edition (64-bit)

Running select cpu_count from sys.dm_os_sys_info returns 40.

The OS sees all 80 threads.

Why is only half the server's processing power being used?

We have the same hardware and software on two servers and they both exhibit the same behavior.

0

3 Answers 3

24

In the SQL Server error log, it should tell you why. I found this on a customer system (really just pasting the message for Google juice):

SQL Server detected 4 sockets with 8 cores per socket and 16 logical processors per socket, 64 total logical processors; using 40 logical processors based on SQL Server licensing. This is an informational message; no user action is required.

I got the likely explanation from here:

For customers with Software Assurance on existing SQL EE Server licenses (or access to them under their current Enterprise Agreements during term) a version of Enterprise Edition was created to enable them to upgrade to SQL Server 2012. This version has technical restrictions limiting an instance to using only 20 processor cores (40 CPU threads with Hyperthreading).

Summary: in the error log, the edition will be reported as either Enterprise Edition or Enterprise Edition: Core-based Licensing. If it says the former, as in the case of the aforementioned customer system, you will need to obtain a core-based licence to use all the available cores.

If that isn't the case and you're already licenced for all cores, check your affinity mask settings, particularly if they were set, and the underlying hardware was upgraded.

2
  • We had a similar problem. We build a VM with 1 socket and 32 cores. Sql would only recognize 8 CPUs. We switch it to 8 sockets and 4 cores the issue went away. We where running enterprise editon sql 2008r2 on windows 2008r2. Mar 16, 2015 at 3:40
  • I had the same problem with a standard version. We had 8 sockets used and it wouldn't use the rest. All we had to do is change the VM and configure 4 sockets 2 cores. Jan 7, 2016 at 22:15
8

Look in the ERRORLOG from when SQL Server first fired up. It'll tell you how many CPU's it's using, and probably why. If you can post the ERRORLOG file somewhere that'll be helpful in seeing what's going on.

0
3

I saw this on another post by Ajmer Dhariwal:

Even if you have Enterprise edition, if the licencing model being applied is Server + Client Access License (CAL) based then the server is limited to 20 physical cores per instance

0