SQL Server's "Total Server Memory" consumption stagnant for months with 64GB+ more available

I have run into an odd issue where SQL Server 2016 Standard Edition 64-bit has seemed to have capped itself off at precisely half of the total memory allocated towards it (64GB of 128GB).

The output of @@VERSION is:


Microsoft SQL Server 2016 (SP1-CU7-GDR) (KB4057119) - 13.0.4466.4 (X64) Dec 22 2017 11:25:00 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows Server 2012 R2 Datacenter 6.3 (Build 9600: ) (Hypervisor)

The output of sys.dm_os_process_memory is:

When I query sys.dm_os_performance_counters, I see that the Target Server Memory (KB) is at 131072000 and Total Server Memory (KB) is at just under half of that at 65308016. In most scenarios, I would understand this to be normal behavior as SQL Server has not yet determined that it needs to allocate any further memory for itself.

However, it has been "stuck" at ~64GB for over 2 months now. During this timeframe we have performed a significant amount of memory-intensive operations on some of the databases, and have added close to 40 more databases to the instance. We are sitting at 292 databases total, each with pre-allocatted data files at 4GB with a 256MB autogrowth rate and 2GB log files with a 128MB autogrowth rate. I perform a full backup once nightly at 12:00AM, and begin transaction log backups Monday through Friday starting at 6:00AM through 8:00PM on an interval of every 15 minutes. These databases are relatively low on their overall throughput, but I'm skeptical that something is awry given that SQL Server hasn't crept up towards the Target Server Memory naturally through new database additions, normal query executions, as well as memory-intensive ETL pipelines that have been ran.

The SQL Server instance itself is sitting atop a virtualized (VMware) Windows Server 2012R2 server with 12 CPU, 144GB of memory (128GB to SQL Server, 16GB reserved for Windows), and 4 total virtual disks that sit atop a vSAN with 15K SAS drives. Windows sits naturally on a 6

I bet you've configured the virtual CPUs in a way that some of the CPU nodes and/or memory nodes are offline.

Download sp_Blitz (disclaimer: I'm one of the authors of that free open source script) and run it:

sp_Blitz @CheckServerInfo = 1;

Look for warnings about CPU and/or memory nodes being offline. SQL Server Standard Edition only sees the first 4 CPU sockets, and you may have configured the VM as something like 6 dual-core CPUs. It'll end up hitting an issue similar to how Enterprise Edition's 20-core-limits cap the amount of memory you can see.

If you want to share sp_Blitz's output here, you can run it like this to output to Markdown, which you can then copy/paste into your question:

sp_Blitz @OutputType = 'markdown', @CheckServerInfo = 1;

Update 2018/04/16 - confirmed. You attached the sp_Blitz output (thanks for that!) and it does indeed show that you have CPU and memory nodes offline. Whoever built the VM configured it as 12 single-core CPUs, so SQL Server Standard Edition is only seeing the first 4 sockets (cores), and the memory attached to them.

To fix it, shut down the VM, configure it as a 2-socket, 6-core VM, and then SQL Server Standard Edition will see all of the cores and memory. This will also reduce your SOS_SCHEDULER_YIELD waits too - right now, your SQL Server is hammering the first 4 cores, but that's it. After this fix, it'll be able to work on all 12 cores.

StackExchange Database Administrators Q#204096, answer score: 54