sp_configure – Displays or changes global configuration settings for the current server

Limit SQL Server memory usage: Use the sp_configure system stored procedure with the max server memory option to limit the amount of memory in the buffer pool used by an instance of SQL Server or MSDE. This will prevent SQL Server from using more than the specified amount of memory, thus leaving remaining memory available to start other applications quickly. You cannot set max server memory to a value less than 4 MB. 16 MB or more is recommended especially if you are using replication. max server memory is an advanced option. You need to enable advanced options before you can use it.

Enable advanced options:
EXEC sp_configure ‘show advanced options’, 1
RECONFIGURE WITH OVERRIDE

Set the maximum amount of memory to 64 MB:
EXEC sp_configure ‘max server memory (MB)’, 64
RECONFIGURE WITH OVERRIDE

Display the newly set configuration:
EXEC sp_configure ‘max server memory (MB)’

Set ‘show advanced options’ back to default:
EXEC sp_configure ‘show advanced options’, 0
RECONFIGURE WITH OVERRIDE

The configuration options table can be found at: http://msdn.microsoft.com/en-us/library/ms189631.aspx

Advertisements

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