Sql Server Restrict Memory usage

With growing of sql server database size and increase in page views of website, you might run in to memory crunch situation.

Please note:  You might run into memory crunch situation for number of reasons like memory leak in code, etc, but in this article we will be focusing on only one scenario that is restricting sql server memory usage.

Cause of Memory crunch situation:

By default if you have not restricted sql server memory usage, it can use upto 2147483647.

Solution:

Restrict the memory consumption of SQL Server by changing this limit to something which is reasonably your server can handle.

If you like commands to change the limit:

— Turn on advanced options
EXEC  sp_configure‘Show Advanced Options’,1;
GO
RECONFIGURE;
GO

— Set max server memory = 3500MB for the server
EXEC  sp_configure‘max server memory (MB)’,3500;
GO
RECONFIGURE;
GO

Reference:
https://sqlserverperformance.wordpress.com/2009/10/29/suggested-max-memory-settings-for-sql-server-20052008/


Special Thanks to my friend Pankaj Mishra for sharing this information.