SQL Server does not consume all the memory: insufficient system memory in the resource group & # 39; internal & # 39;

Essentially, the error is saying that there is not enough memory available for the Sql server to grow its internal buffers; this usually happens if Windows has to paginate a lot or other processes are consuming memory.

You should also remember that just because it tells Sql Server to use a minimal amount of RAM, you can use less if the host operating system needs it most: the host always has priority and the buffer group will be cut if the host starts detecting memory Pressure. For example, in the documentation of the configuration options of the server memory it says:

It is not guaranteed that SQL Server allocates the amount of memory specified in the minimum memory of the server. If loading on the server never requires assigning the amount of memory specified in the server's minimum memory, SQL Server will run with less memory.

I would check to see what other processes are running (for example, Service Broker), and how much stress they are putting on the server. If possible, increase the size of the virtual machine or move some of the other processes to other tables.

Block pages in memory option

It is a common mistake that when you use Lock pages in memory that can never be released. The use of the LPIM configuration prevents Windows from moving the pages of the memory to the virtual memory of the disk (the file on the page). However, it does not prevent the Sql server from reducing its memory footprint if it detects that the host operating system has low memory. The following fragment is taken from the same page from which the previous fragment was taken:

The configuration of this option does not affect the dynamic memory management of SQL Server, which allows it to expand or shrink at the request of other memory employees.

The Sql server uses the memory notification API within Microsoft Windows to detect when the host is running out of system resources. If the host is running low, the SQL server will start trimming the buffer group in response. This is an important fact: it is the SQL server that is releasing Memory in response to an external event – Windows is not taking from the SQL server. The following is taken from the Memory Management Architecture Guide:

When SQL Server uses memory dynamically, it queries the system periodically to determine the amount of free memory. Maintaining this free memory prevents the operating system (OS) from paging. If there is less free memory, SQL Server frees up memory for the operating system.