How to limit SQL Server 2005/2008 memory usage
In today’s article we’ll look at additional steps that we can take in order to manage resources on Microsoft Windows Small Business Server 2008/2011.
I will be looking at managing memory usage of SQL server. These tips can be applied on other standard Windows Servers as well, but SBS is more prone to memory usage issues, since it usually has both Microsoft Exchange and some (or more) versions of Microsoft SQL Server installed as well. Under normal circumstances the best practice says not to have both Exchange and SQL Server on the same server, but SBS is a special case. I already discussed the Exchange memory management in my previous blog post How to limit Exchange memory usage.
Changing the memory usage properties on an SQL Server is a bit more straight forward compared with Exchange.
Open Microsoft SQL Server Management Studio and log in to the SQL server instance. In the Object Explorer right-click the SQL server instance and select Properties. In the Select a page tab click on Memory. Under Server memory options you can change the Maximum server memory
In contrary to Exchange settings, this selection is a simple choice of memory limit in MB. There isn’t really any rule of the thumb here, you selection will pretty much depend on the amount of memory of the system.
While talking about the SQL Server memory settings on an SBS server, we also need to address the settings of the Windows Internal Database. This one being definitely running an all SBS systems, this might be just the one that will need to be adjusted. There’s a slight difference though (or huge, depends how you look at it). To log in to this SQL server instance, you will need to use a special instance name in order to log in to it in SQL Management Studio. Once you have the SQL Management Studio logon screen opened, in the Server Name field type in (or copy) the server name as follows:
\\.\pipe\mssql$microsoft##ssee\sql\query
You will need to use windows authentication, and once logged in, you can follow the steps from the first part of this post to change the SQL instance memory settings.
If you have any questions in relation to this post, you can discuss it further in my new forum topic here.