Thursday, September 15, 2011

How to limit the amount of memory used by Microsoft SQL Server

My good buddy in Bangkok asked me: How can we limit the amount of memory used by SQL Server?
It is a good question, because by default SQL Server will try to allocate as much memory as possible.
Assuming that performance is not an issue for you, (perhaps because you are working on a development box with limited resources) you may want to limit how much memory your instance of SQL Server uses.

To configure this setting, open the SQL Server Management Studio, then connect to the server.
Right click on the Server Name and select Properties, a window will open and here you will able to limit the amount of memory SQL Server uses.

Notice that the amount is specified in MB.

Since this is a server option, you may need to either stop the MSSQLSERVER service.
I didn't try using the 'Reconfigure' statement but it would be worth a try.


1 comment: