Setting Maximum and Minimum Memory for SQL Server 2008 R2

Standard

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

image 

                                                                          Fig 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.

image

                                                     Fig 2

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. 

Summary: –

In this article we learnt about setting maximum and Minimum memory for SQL Server 2008 R2.

Hope this article was useful.

Thanks for reading.

About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s