SharePoint 2010: When SQL memory usage keeps growing…

After a single machine SharePoint 2010 install using the built in SQL Server Express my machine became really sloooooooow. After checking the processes it became clear that SQL server was eating memory. This is the default behavior of SQL Server.

I tried to install the SQL Server 2008 Manager Express, but the installation failed. The SQL Server Express provided with SharePoint 2010 seems to be a newer version than the SQL Server 2008 Express version.

After a long search on the internet I finally found how to set the memory limits for a SQL Server instance through osql.exe.

First thing to do is to determine which instance you want to limit. One way of doing this is by finding the process ID using the built in Task Manager, and then use the Sysinternals Process Explorer to determining what instance is running under that process ID. On my machine .\SHAREPOINT was good enough for connecting to the SQL Server instance used by SharePoint.

1. Launch a command prompt
2. Start the SQL prompt, connecting to the desired instance (e.g. .\SHAREPOINT)
3. osql -E -S SERVERNAME\<INSTANCENAME>
4. Execute the following commands to enable setting of advance options:
   
   USE master
   EXEC sp_configure 'show advanced options',1
   RECONFIGURE WITH OVERRIDE
   GO
   
5. Execute the following commands to set the maximum memory in MB. Replace 200 with your desired setting (I use 200MB):
   
   USE master
   EXEC sp_configure 'max server memory (MB)',200
   RECONFIGURE WITH OVERRIDE
   GO
   
6. Execute the following commands to disable advanced settings, for safety’s sake:
   
   USE master
   EXEC sp_configure 'show advanced options',0
   RECONFIGURE WITH OVERRIDE
   GO
   
7. quit

Disclaimer: All information in this blog post is based on my personal interpretation of information collected at the SharePoint Conference 2009 and experiences with SharePoint 2010 Technical Preview version provided to my company in the PEP program.