SQL SERVER – Using 20 Logical Processors Based on SQL Server Licensing

It is second to human nature to utilize every bit of juice for what we pay. You buy something and you would like to get the best out of it right? If you start thinking from an organizations point of view, they think they should buy something which satisfies their needs. That is the only reason why organizations do capacity planning for their applications. Hardware resources are not cheap and it is difficult to over budget on them. Below is a real world query that had come to me.

One of my friend contacted me and told that he is using task manager and seeing that out of 64 processor only 20 processors are shown as utilized for a SQL Server machine. Whenever someone contacts me for any issue, I always want to know their environments details and ERRORLOG gives me a fair idea about it. If you don’t know where ERRORLOG is then you may want to read my earlier blog.

SQL SERVER – Where is ERRORLOG? Various Ways to Find its Location

So, he sent me an ERRORLOG and I found below message over there.

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

This explains the behavior which he is seeing but he also wanted to fix it. I went ahead and looked in the ERRORLOG’a again and found below on the top.

2015-05-13 11:00:54.72 Server      Microsoft SQL Server 2012 (SP1) - 11.0.3000.0 (X64)
Oct 19 2012 13:38:57
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

This is an enterprise edition of SQL so I was expecting it to use all processors and full power of the machine. I searched licensing guide and found that from SQL Server 2012 onwards, there are two enterprise licenses – Core Based and SERVER/CAL Based. As per my research, CAL based license limits SQL Enterprise to use only 20 physical processors. If hyper-threading is enabled then it would be 40 logical processors.

We have identified that this behavior was correct as per license they have purchased. Since this was their most critical server, they have purchased core based license. They came back to me and asked – “How can we change the installed SQL Server to use Core Based license?”

Answer was simple – perform the edition upgrade using new media Here are the steps:

  • Launch the setup.exe from your setup media or network location.
  • Select Maintenance on the left pane of the setup dialog.
  • Select Edition Upgrade on the right pane.
  • After the standard pre-requisite check you will get a Product Key Page. You need to enter your new product key.

Once complete, the Errorlog showed below.

Microsoft SQL Server 2012 (SP1) - 11.0.3000.0 (X64)
Oct 19 2012 13:38:57
Copyright (c) Microsoft Corporation
Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

If you are using SQL 2012 onwards and having big machine, I would like you to check the ERROLROG and check if you are running into same issue. Else you can also run

SELECT SERVERPROPERTY('Edition')

SQL SERVER - Using 20 Logical Processors Based on SQL Server Licensing Edition-01

This is mostly observed when there is an upgrade from earlier version of SQL Server because you might not be aware of that. Check and comment if this blog has helped you.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Previous Post
SQL SERVER – What are T-SQL Window Functions? – Notes from the Field #082
Next Post
SQL SERVER – Error Msg 511 using SQL Server Sparse columns

Related Posts

9 Comments. Leave new

  • funny but I don’t get the ‘based’ licensing response only the edition and bits

    Reply
    • rik – where?

      Reply
      • When doing your query via T-SQL in SSMS open query box, it only give me the SQL edition and ‘bit’ like 64. I tried this on MS SQL 2012 R1, and older instance: The log does not show the based licensing either. Perhaps I need to turn on that feature?

      • Please share Select @@version output.

  • nakulvachhrajani
    May 30, 2015 12:51 am

    Interesting and useful. Thanks for sharing!

    Reply
  • The other fun issue is that it only uses the *first* 20 cores it finds. Which means, thanks to NUMA, it’s probably using the worst combination of resources available. If you’re stuck with this, you can adjust CPU affinity to properly spread the workload across all the sockets on the system. Otherwise you’ll have a lot of really weird issues. This is, honestly, a really bad default on Microsoft’s part. If they’re going to enforce a lower limit, they need to set the affinity at startup time to something that isn’t horrible.

    Reply
  • hi

    i am facing same issue i have 88 core server but sql is using 40 cour

    i have used this query

    SELECT SERVERPROPERTY(‘Edition’)

    output
    —————–
    Enterprise Edition (64-bit)

    SELECT @@VERSION

    output
    ————-
    Microsoft SQL Server 2014 (SP2-GDR) (KB4019093) – 12.0.5207.0 (X64)
    Jul 3 2017 02:25:44
    Copyright (c) Microsoft Corporation
    Enterprise Edition (64-bit) on Windows NT 6.3 (Build 9600: )

    please guide me , how i can increase core upto 88.

    Reply
  • Hi Abid Ali, as Pinal Dave says SQL Server has been installed/registered via the Server+CAL installer rather then the “Enterprise Edition: Core-based Licensing (64-bit)” installation Media. So you need to download the correct media from your licence portal and perform licence upgrade on your server via the setup.exe which is much better than an uninstall/re-install.

    Reply

Leave a Reply

Menu