Infrastructure at your Service

Stéphane Haby

SQL Server: how to have the number of core-based licenses used on my environment?

This week, a customer asks me to find out how many SQL Server Standard licenses are used in their environment. The licensing is core based and to not be used for Sserver + Cals licenses.
To have an idea, I create a script following the Microsoft document about the licensing here.

This script is valid for the SQL Server 2016, SQL Server 2017 and SQL Server 2019.

If you have another version of SQL Server, please consult the Microsoft documentation before using the script, adapt it and share it with me! 😉

If your host servers are licensed with Enterprise Edition (in the past, it was Datacenter Edition), you don’t need this script. In my case, it was useful to find all Standard Edition used by my customer.
The Microsoft requirement is very easy:

In the script, I search to have the SQL Server edition, the logical CPU count, the number of sockets and the number of cores per socket to do the calculation.
Important: One license is for 2 cores.
I search the information for the SQL Server edition through SERVERPROPERTY(N’Edition’) and the others information with the system view [sys].[dm_os_sys_info].

With these information’s, I will be able to calculate the number of licenses used by every server:

  • The first case is easy with the Express and Developer Edition, no licenses at all.
  • The second case is if you have less than 4 cores, it’s 2 licenses (1 license for 2 cores)
  • The third case is if you have a standard edition and you are limited to 4 sockets and 24 cores
  • The fourth case is if you have a web edition and you are limited to 4 socket and 16 cores

The default is for Enterprise and no limitation on the web or standard edition.

Here the magic query:

SELECT  @@SERVERNAME as [Server Name],SERVERPROPERTY(N'Edition') AS Edition,cpu_count AS [Logical CPU Count],  
             cpu_count/hyperthread_ratio AS [Sockets],  hyperthread_ratio AS [Cores Per Socket], 
    CASE
       -- Developer Edition,Express Edition,Express Edition with Advanced Services = 0 licenses
	   WHEN ((UPPER(Cast(SERVERPROPERTY(N'Edition') as sysname)) like N'%EXPRESS%') OR (UPPER(Cast(SERVERPROPERTY(N'Edition') as sysname)) like '%DEVELOPER%'))
			THEN 0 
	   -- less then 4 cores = 2 licenses for Standard and Enterprise Edition
	   WHEN ((cpu_count/2)<4) 
			THEN 2 
       -- Limited to 4 sockets & 24 cores for Standard Edition 
	   WHEN ((cpu_count/hyperthread_ratio)> 4) AND UPPER(Cast(SERVERPROPERTY(N'Edition') as sysname)) like N'%STANDARD%'  AND 4*(hyperthread_ratio/2) <= 24
			THEN (4*(hyperthread_ratio))/2  
	   WHEN ((cpu_count/hyperthread_ratio)<= 4) AND UPPER( Cast(SERVERPROPERTY(N'Edition') as sysname)) like N'%STANDARD%'  AND 4*(hyperthread_ratio/2) > 24
			THEN 24/2 
       -- Limited to 4 sockets & 16 cores for Web Edition 
	   WHEN ((cpu_count/hyperthread_ratio)> 4) AND UPPER(Cast(SERVERPROPERTY(N'Edition') as sysname)) like '%WEB%'  AND 4*(hyperthread_ratio/2) <= 16
			THEN (4*(hyperthread_ratio))/2  
	   WHEN ((cpu_count/hyperthread_ratio)<= 4) AND UPPER(Cast(SERVERPROPERTY(N'Edition') as sysname)) like '%WEB%'  AND 4*(hyperthread_ratio/2) > 16
			THEN 16/2 
       --Logical cores for Enterprise (unlimited)
	   ELSE cpu_count/2   
     END as [Number of licenses]
FROM [sys].[dm_os_sys_info]

I run the query through a CMS (Central Management Server) and have some very interesting result to analyse:

What we can analyse on this table result:

  • For the server1, only 2 cores but you need to license 4 cores minimum then 2 licenses are needed
  • For server2 and server3, no problem, it’s an Enterprise edition Number of License = number of cores/2
  • For the server4 it’s a standard edition with 4 cores and 4 sockets, no problem Number of License = number of cores/2
  • For the server5, a standard edition but with 6 sockets then limitation is 4 then Number of License = 4*4cores/2
  • For the server6 and server 7, we have an Express Edition and Developer Edition, no license needed

I don’t test all cases but if you use it, I’m very interested by your feedback.
I hope this can help you also to have a good picture of your used core-based licenses in your environment.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Stéphane Haby
Stéphane Haby

Delivery Manager and Senior Consultant