CPU and Memory Allocation on Azure SQL Database Managed Instance
Reviewed By: Ajay Kalhan, Borko Novakovic, Drazen Sumic, Branislav Uzelac
In the current Azure SQL Database Managed Instance (MI) preview, when customers create a new instance, they can allocate a certain number of CPU vCores and a certain amount of disk storage space for the instance. However, there is no explicit configuration option for the amount of memory allocated to the instance, because on MI, memory allocation is proportional to the number of vCores used.
How can a customer determine the actual amount of memory their MI instance can use, in GB? The answer is less obvious than it may seem. Using the traditional SQL Server methods will not provide the right answer on MI. In this article, we will go over the technical details of CPU and memory allocation on MI, and describe the correct way to answer this question.
The information and behavior described in this article are as of the time of writing (April 2018). Some aspects of MI behavior, including the visibility of certain compute resource allocations, may be temporary and will likely change as MI progresses from the current preview to general availability and beyond. Nevertheless, customers using MI in preview will find that this article answers some of the common questions about MI resource allocation.
First glance at CPU and memory on MI
We will use a MI instance with 8 vCores as an example. On the traditional SQL Server, most customers would look at the Server Properties dialog in SSMS to see the compute resources available to the instance. On our example MI instance, this is what we see:
We should right away note that the resource numbers in this dialog, as well as in several other sources (DMVs) described later, can change over the lifetime of a given MI instance. These changes can be relatively frequent. Customers should not take any dependencies, or make any conclusions based on these numbers. Later in the article, we will describe the correct way to determine actual compute resource allocation on MI.
An immediate question is why we see 24 processors here, when we have created this instance with only 8 vCores/processors. To determine the actual number of logical processors available to this instance, we can look at the number of VISIBLE ONLINE schedulers in the sys.dm_os_schedulers DMV:
SELECT COUNT(1) AS SchedulerCount
FROM sys.dm_os_schedulers
WHERE status = 'VISIBLE ONLINE';
SchedulerCount
--------------
8
This is in line with the number of vCores we have for this MI instance. Then why does SSMS show that 24 processors are available?
CPU and memory resources are managed differently on MI
To answer this question, we need to take a high-level look at the MI architecture. Each MI instance runs in a virtual machine (VM). Each VM may host multiple MI instances of varying sizes, in terms of compute resources allocated to the instance.
It is important to note here that all MI instances on a given VM always belong to the same customer; there is no multi-tenancy at the VM level. In effect, the VM hosting MI instances serves as an additional isolation boundary for customer workloads. This does not mean that if a customer creates multiple MI instances, they will necessarily be packed on the same VM. In reality, this does not happen very often. The service intelligently allocates instances to VMs to always provide guaranteed SLAs and ensure good customer experience.
What SSMS shows in the Server Properties dialog is the number of processors and the amount of memory at the OS level on the VM that happens to currently host the instance. This works the same way for the traditional SQL Server, where SSMS also shows OS level numbers. SQL Server error log, which is accessible on MI, shows the same information during MI instance startup:
SQL Server detected 2 sockets with 12 cores per socket and 12 logical processors per socket, 24 total logical processors; using 24 logical processors based on SQL Server licensing. This is an informational message; no user action is required.
Detected 172031 MB of RAM. This is an informational message; no user action is required.
For our example MI instance, this means that there are 24 processors accessible to the OS on the underlying VM. However, given the number of visible online schedulers, the MI instance can only use 8 of these processors, as expected given its current provisioned size.
To reiterate, the number of processors and the amount of memory at the VM level (24 processors and 172031 MB in this example) is not fixed. It can change over time as the MI instance moves across VMs allocated to the customer, for example when it is scaled up or scaled down. These values will, however, always be larger than or equal to the resource values actually allocated to the instance.
But what about memory? Does this MI instance have 168 GB of memory, as shown in SSMS and in the error log? Let’s look at some DMVs.
SELECT cpu_count,
physical_memory_kb,
committed_target_kb
FROM sys.dm_os_sys_info;
cpu_count physical_memory_kb committed_target_kb
----------- -------------------- --------------------
8 176,160,308 48,586,752
SELECT cntr_value
FROM sys.dm_os_performance_counters
WHERE object_name LIKE '%Memory Manager%'
AND
counter_name = 'Target Server Memory (KB)';
cntr_value
----------
48,586,752
Both of these show that the server target memory, which is commonly used to measure the amount of memory available to the instance, is about 46 GB, while the total physical memory at the OS level is 168 GB, as seen in SSMS and in the error log. This shows that not all memory available at the VM OS level is allocated to this MI instance.
On the traditional SQL Server, the usual reason for the target memory to be much lower than the available OS physical memory is configuring a limit on server maximum memory using sp_configure. Is that the case here?
SELECT value_in_use
FROM sys.configurations
WHERE name = 'max server memory (MB)';
value_in_use
------------
2147483647
This large value shows that the maximum server memory for this instance is not limited, and the instance should be able to allocate all available physical memory. What is causing target memory to be much less than the total physical memory for this instance? Is there some other mechanism that can impose a lower limit?
For MI, this mechanism is Job Objects. Running a process such as SQL Server in a job object provides resource governance for the process at the OS level, including CPU, memory, and IO. This resource governance is what allows the service to share the same VM among multiple instances belonging to the same customer, without resource contention and “noisy neighbor” problems. At the same time, this mechanism guarantees a dedicated allocation of vCores and memory for each instance. Memory is allocated according to the GB/vCore ratio for the instance size selected by the customer. There is no overcommit of either vCores or memory across instances on the same VM. In other words, the instance always gets the resources specified during provisioning.
Can we see the configuration of the job object that contains our MI instance? Yes, we can:
SELECT cpu_rate,
cpu_affinity_mask,
process_memory_limit_mb,
non_sos_mem_gap_mb
FROM sys.dm_os_job_object;
cpu_rate cpu_affinity_mask process_memory_limit_mb non_sos_mem_gap_mb
----------- -------------------- ----------------------- --------------------
100 255 57,176 9,728
The sys.dm_os_job_object DMV exposes the configuration of the job object that hosts the MI instance. For our current topic, there are four columns in the DMV that are particularly relevant:
cpu_rate: this is set to 100%, showing that each vCore can be utilized by the MI instance to its full capacity.
cpu_affinity_mask: this is set to 11111111 (in binary), showing that only eight OS level processors can be used by the process hosted in the job object (SQL Server). This is in line with the number of vCores provisioned for this instance.
process_memory_limit_mb: this is set to about 56 GB, and is the total amount of memory allocated to the process within the job object. Note that this is larger than the server target memory, which, as we saw earlier, is about 46 GB. The next column provides the explanation.
non_sos_mem_gap_mb: this is the amount of memory that is a part of total process memory, but is not available for SQL Server SOS (SQL OS) memory allocations, i.e. is reserved for things like thread stacks and DLLs loaded into the SQL Server process space. The difference between process_memory_limit_mb and non_sos_mem_gap_mb is 46 GB, which is exactly the server target memory that we saw earlier.
To elaborate on the last point, even though SQL Server target memory visible in DMVs and in the output of DBCC MEMORYSTATUS is less than the total memory allocated to the instance, this difference, known as the non-SOS memory gap, is still being used by the instance. In fact, a sufficiently large allocation of non-SOS memory is required for the instance to function reliably.
Conclusion
To summarize the technical details above:
1. MI compute resource values shown in SSMS and the instance error log reflect resources at the underlying OS level, not the actual resources available to the MI instance. The resource values at the OS level can change over time, without affecting the resources allocated to the MI instance in any way.
2. MI instance is resource-governed at the OS level using a job object.
3. The sys.dm_os_job_object DMV exposes job object configuration for the MI instance. This is the DMV that should be used to determine the actual compute resources (vCores and memory) allocated to the MI instance, as described above.
We hope that the information in this article will help customers using Managed Instance to accurately and confidently determine the amount of compute resources allocated to their instances, and avoid potential confusion in this area due to architectural differences between the traditional SQL Server and Managed Instance.