Continuing with the series of blog posts on how to find out the 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 available memory & reserved disk space from Azure SQL Managed Instance.
You can find and reference the whole series with the following link http://aka.ms/sqlmi-howto.
In the previous blog post of this How-to series we have focused on the initial hardware properties of the SQL Managed Instance - CPU vCores and in this post we shall continue with hardware properties, focusing on the Memory.
Precise information on the memory is essential for performance optimization reasons and is one of the key properties for any relational database engine.
For the 5th Generation of SQL MI hardware, we are currently providing you with 5.1 GB of RAM per each CPU vCore, meaning that a total amount of RAM for a 4 CPU vCore SQL MI is 20.4 GB and for a 16 CPU vCore SQL MI would be 81.6 GB, and so forth.
If your application needs more RAM to function properly, at the moment of this article writing, on Azure SQL Managed Instance, the only solution is to increase the number of CPU vCores.
For Azure SQL Managed Instance, none of the habitual old DMVs such as sys.dm_os_process_memory or sys.dm_os_sys_info will provide correct answer, because they were not updated to adjust to the reality of the complex Azure SQL implementations.
In order to get the correct information the Job Object, the DMV sys.dm_os_job_object will need to be used.
Job Object is a grouping of processes that need to be managed as a single unit with an explicit resource governance for CPU, memory and IO for the processes.
This is the way how SQL MI is being run in our cloud and extracting information from the corresponding view is the way to have a precise information on the processes within Job Object. Job objects are namable, securable, sharable objects that control attributes of the processes associated with them.
For more information please consult the official documentation for Job Objects.
To observe the total amount of memory attributed to your Azure SQL MI, use the following query by consulting the column [process_memory_limit_mb], meaning that for example, for my 4 CPU vCores Azure SQL Managed Instance I expect to get 20.4 GB of RAM:
SELECT cpu_rate / 100 as CPU_vCores,
CAST( (process_memory_limit_mb) /1024. as DECIMAL(9,1)) as TotalMemoryGB
FROM sys.dm_os_job_object;
There is an important detail that needs to be added to the information extraction, because as in the case of any SQL Server installation, there is a certain amount of memory that will be spent on running the SQL Server itself. This is not the amount of memory that is being spent on the Operating System, this is the amount of memory that is spent on making non-SOS memory allocations of the Azure SQL Managed Instance to function properly.
On Azure SQL MI, currently, there is no possibility of controlling the maximum server memory setting, and, we do reserve some amount of memory in order to run the process as a part of service correctly & successfully. This amount of memory is set aside for thread stacks, DLLs, and other non-SOS memory allocations.
In the DMV sys.dm_os_job_object, this information is exposed through the column [non_sos_mem_gap_mb] containing the reserved amount of memory in MB.
SQL OS (SOS) target memory is the difference between the values in columns [process_memory_limit_mb] and [non_sos_mem_gap_mb].
SELECT cpu_rate / 100 as CPU_vCores,
CAST( (process_memory_limit_mb) /1024. as DECIMAL(9,1)) as TotalMemoryGB,
CAST( non_sos_mem_gap_mb /1024. as DECIMAL(9,1)) as NonSOSMemGapGB,
CAST( (process_memory_limit_mb - non_sos_mem_gap_mb) /1024. as DECIMAL(9,1)) as TotalAvailableMemoryGB
FROM sys.dm_os_job_object;
Notice that the amount of the memory allocated for those processes is not a static percentage and will depend on the size of the SQL Managed Instance, going lower on SQL Managed Instances with more CPU vCores.
Rests to add that consulting the sys.dm_os_job_object DMV on SQL Managed Instance, requires VIEW SERVER STATE permission.
In this blog post we have provided you with a script to consult the total and the total available memory for your SQL MI environment. In the next post of this series, we shall focus on the disk.
Microsoft
