3

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?

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.

| improve this answer | |
  • This is enterprise edition features - as of 2016sp1 the majority of previously enterprise only features are available in all editions, so you may find this support is available in Standard too. Though with 1TB of RAM the OP must be using Enterprise edition anyway, as the memory limits remain (currently 128Gb per instance for standard edition). – David Spillett Oct 15 at 10:53
  • @DavidSpillett Last known document to say large page support is one which I have added and it says not supported in Standard. Similarly if you see Editions and supported features of SQL Server 2019 (15.x) NUMA Aware large pages not supported in standard. I believe it holds to large pages as well. – Shanky Oct 15 at 11:34
  • @Shanky, good catch on the min server setting NOT increasing the amount of memory used at startup. Just set an instance on a test environment to start with 1 GB of memory and it was only coming online with 124 MB – SQLing4ever Oct 15 at 16:08
5

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

| improve this answer | |
  • Thank you for your reply, and no we are not doing anything odd. The machines that I am particularly thinking of are bare metal servers with Failover Clustered Instances running. The main time that I did see MEMORY_ALLOCATION_EXT was when we were running a DBCC checkdb shortly after starting up SQL Server, but we had no PAGEIOLATCH_* waits; we have an all flash storage array that is fairly sizeable. From the sounds of it, there is no good way to artificially grow SQL Server's memory usage to get to max server memory quickly. – SQLing4ever Oct 14 at 22:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.