Configuration Manager SQL Server 2012 Memory Limit. The server options min server memory and max server memory can be set to span a range of memory values. This method is useful for system or database administrators to configure an instance of SQL Server in conjunction with the memory requirements of other applications, or other instances of SQL Server that run on the same host.
NOTE: The min server memory and max server memory options are advanced options. If you are using the sp_configure system stored procedure to change these settings, you can change them only when show advanced options is set to 1. These settings take effect immediately without a server restart.
Use min_server_memory to guarantee a minimum amount of memory available to the SQL Server Memory Manager for an instance of SQL Server. SQL Server will not immediately allocate the amount of memory specified in min server memory on startup. However, after memory usage has reached this value due to client load, SQL Server cannot free memory unless the value of min server memory is reduced. For example, when several instances of SQL Server can exist concurrently in the same host, set the min_server_memory parameter instead of max_server_memory for reserving memory for an instance. Also, setting a min_server_memory value is essential in a virtualized environment to ensure memory pressure from the underlying host does not attempt to deallocate memory from the buffer pool on a guest SQL Server virtual machine (VM) beyond what is needed for acceptable performance.
NOTE: SQL Server is not guaranteed to allocate the amount of memory specified in min server memory. If the load on the server never requires allocating the amount of memory specified in min server memory, SQL Server will run with less memory.
Use max_server_memory to guarantee the OS does not experience detrimental memory pressure. To set max server memory configuration, monitor the overall consumption of the SQL Server process in order to determine memory requirements. To be more accurate with these calculations for a single instance:
- From the total OS memory, reserve 1GB-4GB to the OS itself.
- Then subtract the equivalent of potential SQL Server memory allocations outside the max server memory control, which is comprised of stack size * calculated max worker threads. What remains should be the max_server_memory setting for a single instance setup.
Config
1. Log in to your system with SQL Server. Find in All Programs SQL Server Management Studio and run it;
2. Connect to your instance by clicking Connect;
3. Click the Right Mouse Button and choose Properties;
4. Go to Memory;
5. There you can set Minimum server memory (in MB) and Maximum server memory (in MB). After all, changes click OK;
6. After this change we need to restart the instance. Right Mouse Button click and select Restart;
7. When you get a pop-up, click Yes;
When restarting is complete, close SQL Server Management Studio.
