Posted on November 1, 2021

With SQL Managed Instance you will need to consider your configuration requirements in terms of core count for the CPU and memory, which we all know that the MIN/MAX settings for SQL is so important.

With Managed Instances when using Gen5 hardware architecture you have 5.1GB of memory per vCore, meaning that 4-core instance will have 20.4GB memory for max server setting, will that be enough? Only you will know your workloads but don’t fear as this increases with cores, you can get quite a bit of RAM assigned. (for extra cost obviously)

Let’s check my setup.

With 4 vcores I login via SSMS and run:

SELECT name, value, value_in_use, [description] 
FROM sys.configurations
WHERE name like '%server memory%'
ORDER BY name OPTION (RECOMPILE);

This does not say much other than 16MB will always be the minimum and the 2147483647 figure means lets just use everything available that can be allocated to SQL server.

This query which uses the DMV below uses a concept called job objects and has better details. https://docs.microsoft.com/en-gb/windows/win32/procthread/job-objects?redirectedfrom=MSDN

For resource governance requirements.

SELECT cpu_rate,
cpu_affinity_mask,
process_memory_limit_mb
FROM sys.dm_os_job_object;

Cpu-rate of 400 means 4 cores we selected, affinity mask 15 means first 4 processor cores using 1111 0000 binary equates to the mask of 15 and the last figure is the memory SQL is allowed.

Compare this to 8 vCores:

SELECT cpu_rate,
cpu_affinity_mask,
process_memory_limit_mb
FROM sys.dm_os_job_object;

Well as you can see the differences above, 800 matches the 8 vCores, the affinity mask is 255 meaning 11111111 ( 8 in use) and the memory increase is expected.