SqlServerMemory: Auto memory on Azure VMs is off by an order of magnitude on large VMs

[GArionMSFT]

Details of the scenario you tried and the problem that is occurring:
Provision an Azure VM of type: Standard_E32_v3 VM (Windows) (with 256GB RAM) and have xSQLServerMemory automatically set max memory.
Expected would be that it sets SQL Server max memory to about 200+ GB RAM, actual result: it sets to about 27GB RAM.

The reason for that is that the script uses
((Get-CimInstance -ClassName Win32_PhysicalMemory).Capacity | Measure-Object -Sum).Sum
to get the total amount of RAM. On Azure, on that VM size that is around 30~ GB RAM.

A more correct way of getting the total amount of memory available would be:
(Get-Ciminstance Win32_OperatingSystem).TotalVisibleMemorySize
(to note - the result here is in KB, vs Bytes as with Win32_PhysicalMemory.Capacity)

The DSC configuration that is using the resource (as detailed as possible):

        xSQLServerMemory Set_SQLServerMaxMemory_ToAuto
        {
            Ensure = 'Present'
            DynamicAlloc = $true
            SQLInstanceName = $SQLInstanceName
        }

Version of the Operating System, SQL Server and PowerShell the DSC Target Node is running:
Windows Server 2016, PS 5.0

What module (SqlServer or SQLPS) and which version of the module the DSC Target Node is running:
SQL Server 2016 SP1

Version of the DSC module you're using, or 'dev' if you're using current dev branch:
xSqlServer 7.1 - but this reproes on all version after that as well

[johlju]

I have tested this on a physical and it looks okay to me.

PS > [Math]::Round((Get-Ciminstance Win32_OperatingSystem).TotalVisibleMemorySize / 1MB)
192

And on a VM with less memory.

PS > [Math]::Round((Get-Ciminstance Win32_OperatingSystem).TotalVisibleMemorySize / 1MB)
8

[johlju]

I label this one as 'good first issue'. If someone new to contributing wants to take something on, this is a good one.

[brwilkinson]

Looks like this class might be best here:

This looks to be reliable in Azure.

(gcim win32_computersystem).TotalPhysicalMemory

[SQLHorizons]

Seen a similar type of issue on aws m5 EC2 machine type, where Win32_PhysicalMemory is returning 0 for capacity.

aws' feedback was to use the following:

((Get-CimInstance -ClassName win32_ComputerSystem).TotalPhysicalMemory | Measure-Object -Sum).Sum

[johlju]

This is about to be resolved in PR #1285. But since there have been reports that the change is better for VM's I thought I would verify that it works for physical servers as well. Since I don't have access to physical servers with a lot of memory I asked a friend.
His conclusion was that the change will work for physical as well. He did note that the suggested change will return another value than our previous way (before this change) of reporting memory, see screenshots. Our thought it that the suggested change in the PR #1285 returns the OS available memory, and will not be exactly the same amount as the actual physical memory due to some reserved memory. My conclusion is that this change will return a more correct number of available memory.

He tested on Windows Server 2012 R2 (HP Gen8 128GB) and Windows Server 2016 (HP Gen10 384GB).

Windows Server 2012 R2:

Windows Server 2016 R2: