Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

migrated from stackoverflow.com Sep 16 '13 at 4:59

This question came from our site for professional and enthusiast programmers.

    
Wow.What sort of database is using this type of hardware –  Kamran Shahid Sep 15 '13 at 16:15
    
Dumb question, but are you using a non-standard affinity mask? –  Ben Thul Sep 15 '13 at 19:18
add comment

2 Answers

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.

share|improve this answer
add comment

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.

share|improve this answer
    
Can you please tell me where I can find the ERRORLOG? –  adinas Sep 18 '13 at 6:40
    
You'll find the ERRORLOG in management studio under Management > SQL Server Logs > Current. Also it'll be in a file on the hard drive in C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Logs by default. –  mrdenny Sep 23 '13 at 21:20
    
Thanks mrdenny. –  adinas Sep 24 '13 at 7:11
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.