How-to find out the SQL MI number of CPU vCores

[NikoNeugebauer]

Continuing with the series of blog posts on how to find out the necessary information about Azure SQL Managed Instance (SQL MI) for your automation scripts and tools, started with How-to find out the SQL MI Service Tier basic details with T-SQL, this article will focus on extracting information on the CPU vCores from Azure SQL Managed Instance.

You can find and reference the whole series with the following link http://aka.ms/sqlmi-howto.

There are multiple ways in determining the number of vCPU Cores in Azure SQL Managed Instance and we are providing you with a couple of them, since besides the used DMV, they vary in their security requirements – starting with VIEW SERVER STATE (the most common requirement) and going down to a much simpler access to the [master] database and the underlying SQL MI server resource statistics DMV.

Using sys.dm_os_nodes Dynamic Management View

For a lot of time, a traditional way of looking into the available hardware/virtualized resources on SQL Server was the sys.dm_os_nodes DMV, which shows the information on the SQLOS and mimic hardware processor locality. The sys.dm_os_nodes DMV gives details on such components as worker count, CPU & Scheduler affinity masks, and serves to determine average number of tasks per scheduler per node, thus allowing to determine a potential raise of pressure on the scheduler.

Using this DMV is a valid way for determining the number of available CPU vCores, like is being shown below - by listing ONLINE nodes only, while the details are stored in the [cpu_count] column:

SELECT 'This SQL Managed Instance has ' + CAST(cpu_count AS VARCHAR(10)) + ' CPU vCores.' as CPU_vCores
FROM sys.dm_os_nodes WITH (NOLOCK)
WHERE node_state_desc = N'ONLINE'
OPTION (RECOMPILE);

The result for my small Azure SQL MI with 4 CPU vCores is shown on the image on the left, representing text with information that there are indeed 4 CPU vCores.

Notice that currently a VIEW SERVER STATE permission is required in order to use this method.

Using sys.dm_os_schedulers Dynamic Management View

Another great way of determining the number of the CPU vCores is by using the DMV sys.dm_os_schedulers, which will list detailed information for each of the CPU schedulers, and each of the schedulers is mapped to an individual processor. To get the correct result, we are filtering just the schedulers that are visible online:

SELECT 'This SQL Managed Instance has ' + CAST(COUNT(*) AS VARCHAR(10)) + '  CPU vCores.' as CPU_vCores
FROM sys.dm_os_schedulers
WHERE status = 'VISIBLE ONLINE';

This query returns the same result is returned as previous one, delivering the correct information on the number CPU vCores.

Same as for the sys.dm_os_nodes DMV, a VIEW SERVER STATE permission is required to use this method.

Using sys.dm_os_job_object Dynamic Management View

The number of CPU vCores is also discoverable through the sys.dm_os_job_object DMV, that is at the moment of this article publishing is exclusive to our PaaS offerings - Azure SQL Database & Azure SQL Managed Instance. 

This DMV displays the information about the configuration of the Job Object where SQL Managed Instance is running, providing with details on the accessible resources. A job object is a Windows construct that implements CPU, memory, and IO resource governance at the operating system level. For more information about job objects, see Job Objects.

To get the information on the CPU vCores we need to use the [cpu_rate] column, dividing the output through 100.

SELECT 'This SQL Managed Instance has ' + CAST(cpu_rate / 100 AS VARCHAR(10)) + ' CPU Vcores.' as CPU_vCores
FROM sys.dm_os_job_object;

Same as for both previous methods, a VIEW SERVER STATE permission is required to use this method.

Using sys.server_resource_stats DMV in the [master] database

The last method we shall consider in this post is the one that falls back to already known DMV - sys.server_resource_stats that returns CPU usage, IO, and storage data for Azure SQL Managed Instance. By extracting the last available data from the [virtual_core_count] column we shall obtain the desired information on the number of CPU vCores:

SELECT TOP(1) 'This SQL Managed Instance has ' + CAST(virtual_core_count AS VARCHAR(10)) + ' CPU vCores.' as CPU_vCores
FROM master.sys.server_resource_stats WITH (NOLOCK)
ORDER BY end_time DESC
OPTION (RECOMPILE);

An important distinction to this method is that it only requires the access to the [master] database and even though this one must be given very carefully, it is quite different from VIEW SERVER STATE and is a preferred way of getting the details on the CPU vCores, because it shows actual used detail, which was updated seconds/minutes ago.

In this blog post we have provided you with just 4 T-SQL scripts allowing you to extract the number of CPU vCores for your SQL MI environment. In the next post of this series, we shall focus on the memory.