In this article you will learn about setting Maximum and Minimum memory for SQL Server 2008.
By default SQL Server will grow its memory as the server memory grows. Usually server accumulates 200 to 300 MB of free space. The problem arises when Windows need additional memory and SQL Server will be slow in releasing this additional memory.
By setting the Maximum and Minimum Buffer size of SQL Server you can over come this problem.
Note : – Max and Min buffer size of SQL Server 2008 R2 are in MB.
Max and Min Server Memory : -
Buffer Pool is the memory that SQL Server uses for most of its requirements. Setting maximum server memory is always recommended.
Step 1: – Open SSMS (if not opened)
Step 2: – Right click on the Server Instance and click Properties as shown in below Figure 1
Step 3: - Navigate and click Memory page. You can set Minimum and Maximum server memory (in MB) as shown in below Figure 2 and click OK to save the changes.
Above changes can be made with query.
Below are the query to make the changes for Max and Min memory (in MB)
Max Memory (MB)
To set Max memory to 3 GB
sp_configure 'max server memory (MB)', 3144 go reconfigure
Min Memory (MB)
To set Min memory to 1 GB
sp_configure 'min server memory (MB)', 1048 go reconfigure
Please note that
- You try these example on local machine first and not on business impact server
- Consult with your DBA before making any changes in production SQL Server Instances.
- Changes to server properties and setting may affect the performance, security and availability of SQL Server instance. Before making any changes consult Product documentation.
In this article we learnt about setting maximum and Minimum memory for SQL Server 2008 R2.
Hope this article was useful.
Thanks for reading.