Tip of the Day

Tip of the Day #5 (SQL Server memory usage)

You can limit the amount of memory that SQL Server uses by using the sp_configure stored procedure. By limiting the amount of memory that SQL Server is permitted to use it means that more memory is available to other applications or other instances of SQL Server. In fact books on-line recommends setting the minimum and maximum memory used on each instance of SQL Server running on the same machine as SQL Server does not make any attempts to balance memory usage across instances.

In order to use this you must be in an advanced mode. To set this up use:

EXEC sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE 

 

Next, to make the actual change you need the following:

EXEC sp_configure 'max server memory (MB)', 512
RECONFIGURE WITH OVERRIDE 

 

The above example will set the maximum amount of memory the server will use to 512MB. The RECONFIGURE WITH OVERRIDE is necessary in order for the change to take effect immediately. If it is missed out then the change won’t take place until the SQL Server is restarted.

If you want to check that the change has taken place you can use the following:

EXEC sp_configure 'max server memory (MB)' 

 

This will just display the current setting. You will get a result set that looks something like this:

SQL Server 2005 memory options result set

The congif_value is the value that the SQL Server is currently configured with. However, it may not be what is currently in force. The run_value shows you what is currently in force.

SQL Server 2005 memory options dialogIf you don’t want to type so much SQL yourself, then you can do the same in the SQL Server Management Studio. Right-click the server in the object explorer and select “properties” from the context menu. This will bring you up a dialog with all the server level properties in it. Go to the “memory” page and you can set the values that you want there. There are a couple of radio buttons that will allow you to switch between the currently configured value and the running value. By pressing Okay the updated value is applied to the server immediately.

For more information:

Technorati Tags: ,

4 thoughts on “Tip of the Day #5 (SQL Server memory usage)

  1. At last, a page that actually tells me how to config the min/max memory settings, rather than witter on about the concepts – Thank you!!

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