thaxor

We’re having a custom app developed for us that was having some speed problems after the server had been up for a while. I downloaded Sysinternals Process Explorer http://technet.microsoft.com/en-us/sysinternals/bb896653.aspx and started digging around for problems. (haven’t mastered performance monitor yet) Tip: if you hover over the sqlservr.exe in Process Explorer it will give you the instance name in a hover balloon so you know which one is eating up memory.

I found the SBSMonitoring instance of SQL using 1.2 GB ram. For an application that looks relatively simple that amount of ram consumption seemed too high. To fix the problem I did some digging and found these pages:

http://msdn.microsoft.com/en-us/library/ms178067.aspx
Use Performance Monitor to set the “correct” ram usage on the SQL Server instance by using  the SQLServer:Buffer Manager object. Or just guess randomly like me.

http://dogriley.blogspot.com/2005/08/sqlservrexe-high-memory-usage-for.html
Set the maximum ram though CMD prompt

If you’ve got SQLServer Management Studio installed you can do it though GUI instead of CMD.

Connect to the instance of SQL, in this case “ServerName\SBSMONITORING”
Right click the Database instance and select properties
Then click the Memory section and put in your “Maximum Server Memory” (I set mine to 100MB)

Since our staff uses sharepoint VERY little I decided to limit that instance to 500MB. Connecting to that with SSMS wasn’t as easy as the SBSMonitoring instance. If you type in “ServerName\MICROSOFT##SSEE” it doesn’t connect. However this text does work “\\.\pipe\mssql$microsoft##ssee\sql\query”