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?