by Malaika Consultants LLC
7. July 2010 01:12
Typical problem
While working with a client, recently we had an issue where the web server was just getting unresponsive for no apparent reason. This server was an IIS box serving several websites and of course having a large number of SQL Server databases. When we looked at the server resources, we found SQL Server was occuping good 3GB of the available 4GB of RAM ! Huh ? Yes!
Obviously there was some leak somewhere, some query taking more resources and not releasing it. We had to investigate that, but had a situation where there are several non-responsive websites.
What are your options?
SQL Server 2008 (and older versions as well) allow you to specify the maximum memory utilized by the sql server process before it is recycled. Right-click on "Properties" for your SQL server through SQL Server Management Studio. Click on "Memory" and then put in the value that works for you in the "Maximum Server Memory (in MB)" option.
Note : The default value is 2147483647 MB which pretty much gives a free rein to SQL Server to do as it pleases. The minimum amount of memory you can specify for max server memory is 16 megabytes (MB).
NOTE : This is not a solution to your memory problems, but only the first step. Remember the SQL Server process will be recyled once the maximum memory limit is reached, and it will need to use resources to start everything it needs for the process to be recycled.
Subscribe