MS SQL Server - Increase allocated memory

Question

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?

Answer 1

I am quite aware that if I set min memory up higher that SQL Server will consume the memory all at once.

No this is not correct, min server memory has nothing to do with how fast SQL Server will consume it if set. Min server memory signifies the minimum amount of memory, if consumed, after which SQL Server will not release memory below this value under normal conditions if asked to do so. Once min server value is reached SQL Server will not trim its caches and release memory below this value.

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.

Yes correct, just to add SQl Server in certain conditions can also consume memory greater than the value specified in max server memory. You should read Memory Management Guide

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

There is other way which forces SQL Server to reserve more memory during startup and this is called "large pages". This is enterprise edition features and allows SQL Server to quickly reserve some free memory during startup, the SQL Server service account should also have locked pages in memory privilege because allocation of large pages is done by VirtualAlloc() function of windows OS and OS should have more than 8 GB RAM, if these conditions are met automatically Large page allocation is enabled. Once enabled it will appear as below in errorlog

2009-06-04 12:21:08.16 Server      Large Page Extensions enabled.
2009-06-04 12:21:08.16 Server      Large Page Granularity: 2097152
2009-06-04 12:21:08.21 Server      Large Page Allocated: 32MB

The normal page size of memory is 4 KB on X64 system but when large page is enabled the size will be 2 MB. Also note that LargePageSupport is enabled and used by the engine even if you don’t enable trace flag 834. But not much memory is used for this and buffer pool memory is not used unless trace flag 834 is enabled. See the blog shared.

Answer 2

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).}