SQL Server 2008 Memory Management

Once again, hello all: 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.

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.

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.

Dirk

Category:Microsoft Category:MsSQL