SBS 2003, Tweak

SQLSERVR.exe using lots of memory

On a Windows Small Business Server 2003 server (RTM, SP1, SP2, R2) you may find that alot of memory is used straight out of the box.

On a few of my client sites, I have seen this happening recently, causing some performance issues for LOB applications. If have found SQLSERVR processes to be the culprit at both of these sites, with some instances using up to 500mb.

SBS uses instances of MSDE for a few of its core tasks:

  • WSUS (For centrally managing and reporting on the Windows Update process to Small Business Clients)
  • MSFW (For the ISA Server 2000 / 2004 instance shipped with SBS Premium)
  • SBSMONITORING (For the inbuilt performance and usage reporting)

None of these instances require so much memory! I have no problem with a SQL / MSDE instance using memory when it is for a LOB application, but for maintenance, reporting, and logging?!

To limit the amount of memory each instance can use:

  1. Determine which instances you wish to limit. I do this by finding the process ID using the built in Task Manager, then determining what instance is running under that process ID with Sysinternals Process Explorer.
  2. Launch a command prompt
  3. Start the SQL prompt, connecting to the desired instance (e.g. SBSMONITORING)
    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 100 with your desired setting (I use 100MB):
    USE master
    EXEC sp_configure 'max server memory (MB)',100
    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

Within about 30 seconds the instance should have dropped it’s memory usage to within about 50mb of your desired setting. Changing this actually changes the size of the dataset that is cached in to memory, there is still other overhead that means your active memory usage may still be around 175mb.

Thanks to Tokahao’s post at the WSUS Forums for this info.