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