How to manually set the maximum memory usage of SQL Server

By default, SQL server uses unlimited memory usage. SQL Server increases or compresses the buffer cache based on server activity to keep the available physical memory between 4 MB and 10 MB.

If it is only for development and use on your own machine, it is not a server, and SQL cannot occupy memory without limit.

There are two main ways to manually set the SQL Server Memory options:

• The first method is to set min server memory and Max server memory to the same value. This value corresponds to the fixed amount of memory allocated to the SQL Server Buffer Pool after this value is reached.

• The second method sets min server memory and Max server memory to a memory range. This method requires the system or database administrator to configure the SQL server instance and consider other applications running on the same computer.ProgramMemory is very useful.

Min Server Memory ensures the minimum memory available in the buffer pool of the SQL server instance. SQL Server does not allocate the memory size specified by Min server memory at startup. However, unless the value of Min server memory is reduced, when the memory usage reaches this value due to client load, SQL Server cannot release the memory from the allocated buffer pool.

Max Server Memory avoids the use of more memory than the specified memory in the SQL Server Buffer Pool, so that the remaining available memory can be used to quickly start other applications. SQL Server does not allocate the memory size specified by Max server memory at startup. The memory usage increases as the SQL server needs to reach the value specified by Max server memory. Unless you increase the value of Max server memory, SQL Server cannot exceed the memory usage.