MS SQL Server - Increase allocated memory

My problem is that SQL Server takes a long time to ramp up it's memory usage for instances with TB worth of RAM, all the while we get intermittent waits of MEMORY_ALLOCATION_EXT that slow down our processing until SQL Server reaches its max memory.

We have Failover Clustered Instances (FCI) of SQL Server 2019 Enterprise Edition with terabytes worth of memory on the nodes. In usual use cases we only allow 1 instance of SQL Server per node, and so we set max server memory close to ~85% of the memory on the node, but we also set min server memory relatively low just in case SQL Server fails over to another node and needs to be brought online with a reduced memory footprint to limp along.

• I am quite aware that if I set min memory up higher that SQL Server will consume the memory all at once. Turns out SQL Server does not allocate the min memory at startup.

• I am aware that SQL Server will dynamically consume more memory from the OS as it needs it and that eventually it will reach the max server memory.

• I am aware that running a big query or DBCC checkdb that pulls in a lot of data will force SQL Server to consume more memory from the OS.

Are there any other ways to force SQL Server to increase its memory usage quickly?

SQL Server will not use memory for buffer pool (its biggest use) until it needs to load pages from storage because they are referenced. Unless you are doing something odd to make memory allocation slow[†] then your delay is unlikely to be that but reading the data that it is being allocated in order to hold. I suspect that at the same time as those memory allocation waits, you have matching IO related waits.

To force SQL server to load things into memory, and therefore to allocate memory if available and needed, access things so that they need to be loaded. After starting your SQL instance, run a read-only workload so that your application's normal common working set is loaded into the buffer pool.

Don't just SELECT * FROM HugeTable as this is likely to load a bad balance of data (not the applications core working set) so the core working set will still need to be loaded from disk/network on next access anyway so you'll have the same IO delays. Also if HugeTable is truly huge SQL Server may see that it'll not all fit and not bother keeping a significant amount of it in memory anyway[‡].

[†] Running in a VM on an overloaded host so what looks like real RAM is really on-disk pages?!

[‡] Under normal circumstances you don't want an unexpected table/index scan on a large object to evict everything else from memory. A limit on how much is kept from one object for one query is an optimisation to try stop this from happening).

StackExchange Database Administrators Q#278075, answer score: 5