SQL Server memory

Question

I have a query related to memory. Two SQL server Instances(2016) are running in a server(windows server 2016). The server contains 64 GB RAM. For instance1 25GB(MAX memory) is allocated and for instance2 25GB(MAX memory) is allocated. The instance1 is using 25GB of RAM. If SQL server faces memory pressure whether it will use memory beyond 25GB or not?

Answer 1

If SQL server faces memory pressure whether it will use memory beyond 25GB or not?

If SQL Server sees memory pressure, then it will attempt to trim memory. It should not allocate more, but there is a chance that it won't be able to trim memory in a timely manner, depending on the server, configuration, and run-time issues occurring.

Answer 2

The "max server memory" setting does NOT control all memory allocated by the SQL Server service. It can use more than the max server memory setting.

Yes, it will try to trim memory if the OS triggers a "low memory" message. However, it will attempt to run the memory right back up to the max memory setting.

Please see:
https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/server-memory-server-configuration-options?view=sql-server-ver15

Answer 3

Hi @kumar-9444 ,

>>>If SQL server faces memory pressure whether it will use memory beyond 25GB or not?

No. By default, SQL Server can change its memory requirements dynamically based on available system resources. The instance then continues to acquire memory as needed to support the workload. As more users connect and run queries, SQL Server acquires the additional physical memory on demand. A SQL Server instance continues to acquire physical memory until it either reaches its max server memory allocation target or the OS indicates there is no longer an excess of free memory; it frees memory when it has more than the min server memory setting, and the OS indicates that there is a shortage of free memory. Refer to Dynamic Memory Management, Effects of min and max server memory.

---

If the response is helpful, please click "Accept Answer" and upvote it, thank you.

Answer 4

If SQL server faces memory pressure whether it will use memory beyond 25GB or not?

I guess your question is will, instance 2 ( for example) if it faces memory crunch, will force instance1 to trim down its memory consumption and provide that trimmed memory to instance 2 . Well in that case there are 3 scenarios. So instance2 faces memory pressure and instance 1 has following configuration

1. Instance 1 has min and max server memory set to 25 GB and locked pages in memory allocated:--. In this case instance 2 or SQL Server will not be able to force instance 1 to release memory as min server is set to max and LPIM is there. Please note SQL Server does not trims down below min server memory setting.
2. Instance 1 has min server memory set to default and max server memory set to 25 GB and locked pages in memory allocated:--. In this case instance1 can release memory but being LPIM this would make process slow and the mileage gained would not be much
3. Instance 1 has min and max server memory set to 25 GB and NO locked pages in memory allocated:-- In this case if instance2 is facing lot of memory pressure chances are that instance one memory can be paged to disk and freed memory can be given to instance 2. This is because SQL Server will not go below min server value any force would result in paging.
   4. Instance 1 has min server memory set to default and max server memory set to 25 GB and NO locked pages in memory allocated:-- Instance one will oblige happily and trim down till min server value which is few MB's. But even before that value is reached instance 2 would have its memory requirement catered.