Tuesday, June 02, 2015

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.

No comments:

Most Recent Post

Subscribe Blog via Email

Enter your email address:



Disclaimers:We have tried hard to provide accurate information, as a user, you agree that you bear sole responsibility for your own decisions to use any programs, documents, source code, tips, articles or any other information provided on this Blog.
Page copy protected against web site content infringement by Copyscape