Recently I had a problem with SQL server. I was sifting through the processes in Task Manager a few days ago ordered by memory consumption. At the top of the list for memory consumption was SQL Server (sqlserver.exe) weighing in at 200 megabytes of memory. I decided to look past that one since 200 megabytes isn't too unreasonable for SQL, especially when it's hosting the data for quite a few SharePoint web applications.
Today, I checked again. After my server had been online for two and a half days, SQL server had grown to over 650 megabytes of memory (653,224 KB specifically). Seeing as how I have not made any changes to my local SharePoint environment in that time (I'm currently developing a non-SharePoint related project), I decided to look into putting a cap on the memory consumption of SQL. Originally I had 2 gigabytes of ram for my server. I added an extra gigabyte to that and SQL took up the additional space.
As it turns out, one can put a maximun and a minimum limit on SQL. Here's how.
*NOTE* This is done in SQL Server 2008 so the specified actions may not be available if you are using an older version such as 2003.
- Open up SQL Server Management Studio 2008
- Type in the information to connect to the server that has SQL server running on it and click connect.
- Right click the server name
- Click Properties
- Select Memory on the left side of the window that comes up
- Under Server Memory Options, adjust the minimum and maxiumum memory settings to what you need.
*NOTE* If you are running a 32-bit operating system and need to allocate more than 4 gigabytes of RAM to SQL, be sure to check the "Use AWE to allocate memory" checkbox.
- Click OK
- Right Click the server name again
- Select Stop from the menu
- Click necessary buttons to get through the prompts
- Right Click the server name yet again
- Select Start from the menu
- Click the necessary buttons to get through the prompts
And that's it. Mine (as in the screenshots) has yet to go over 300 megabytes of memory consumption.
Thanks for reading.
Aaron Ball
