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.