SQLSERVR.EXE memory usage on Small Business Server 2003

[Fardringle]

I've been given the task of troubleshooting an SBS 2003 server for a small office. Users are reporting long delays accessing data and program files stored on the server. I discovered that the sqlservr.exe process running for Veritas BackupExec 10 is using a very large portion of the physical RAM in the server (1.6GB used by this one process out of 3GB total).

Microsoft says that this is normal activity for a dedicated SQL server since the server wants to cache as much data as it can for database users. However, this is the domain controller and file server for the office and SQL is only running because BackupExec starts it automatically to store the backup logs. I can't imagine that the SQL service would need more than a few MB of RAM at most to track what BackupExec is doing.

I know that SQL server can be told to use a limited amount of system memory through the Enterprise Manager. Unfortunately, this server is SBS 2003 Standard and has the MSDE (limited) version of SQL server 2000 on it so Enterprise Manager is not available. I've done quite a bit of searching on the web and can't find another way to administer the SQL server. Is there any way to tell SQL to stop monopolizing the available memory on the server without the Enterprise Manager? Or is there a way to obtain and install Enterprise Manager without upgrading the operating system to SBS 2003 Premium?

 

[RebateMonger]

There's been a LOT of discussion about SBS and SQL/MSDE allocated memory. SBS uses a lot of SQL processes and often issues Alerts about excessive memory usage.

Susan Bradley discussion how limit SQL memory usage through the Command Line.

But I'd be surpised if this is the cause of your long data access delays.

ALL SBS Servers are DCs, and almost all of them are the primary file server for offices of five to fifty people. Slow file access has never been a problem on any that I've seen. Of course, most small offices run their backups at night.

If you disable Backup Exec, what happens?

 

[Fardringle]

If I kill the sqlservr.exe process that is taking up 1.6GB the server almost immediately begins responding to client requests normally again, so I do believe that it is at least related to the problem. I'll try disabling Backup Exec itself as well as the command line option in that link to see what happens as soon as I am in front of the server again. Thanks.

 

[ebaycj]

if you can connect to it through query analyzer, try running EXEC sp_configure

That might give you some config settings that you can set. Not 100% sure if MSDE supports it but i bet it does.
Google sp_configure for more info.

basically there is a "max memory" setting in there (don't remember exactly what it's called but its something like that), you want to set that to say 768MBytes or so.

 

[Fardringle]

Turning Backup Exec off completely makes no difference in the amount of memory that the sqlservr process is using, even though tasklist /svc says that the task is in fact associated with MSSQL$BKUPEXEC. Backups are set to run at 11 PM so in my opinion this process shouldn't really even be active during the day when it is causing problems for the users, and certainly not monopolizing resources on the domain controller.

I used the instructions listed in the link that RebateMonger provided and the OSQL interface actually said that this process was limited to a maximum memory usage of 255MB. Despite that limit, the service was continuously using 1.6GB or more of RAM. I set the service to use a max of 50MB since that is more than the total size of every SQL database file on the server (there aren't many, mostly in the Backup Exec folders, and all are small). Memory usage by sqlservr.exe immediately dropped to 40MB in Task Manager. That's still more than I think this particular service should need considering what it is doing, but it's certainly more acceptable than 1.6GB!

I'll have the office users do some checking on Monday to see if performance on the server has improved.

 

[RebateMonger]

I'm assuming you have SBS Monitoring turned on? It will send Alerts for most events that would cause performance problems, like CPU, memory and disk utilization.

 

[Fardringle]

In case any of you were wondering...

MSSQL$BKUPEXEC was set to use 255MB of RAM and was using 1.6GB continuously. MSSQL$SBSMONITORING was set to you 756MB and started using more than 2GB as soon as I set $BKUPEXEC to a limit of 50MB. I set both processes to a maximum memory usage of 50MB. They are now both using between 70 and 100MB (more than they are supposed to but a much more reasonable range) and users in the office are reporting a HUGE difference in speed of access to files on the server. For example, one directory with around 4GB of scanned image files (used to store client invoices) was taking anywhere from 10-30 seconds to display. Now it takes less than 2 seconds. Nothing else has been changed on the server so it had to be the SQL processes even though they are not supposed to affect performance at all.

Thank you very much for your help. I'd still like to know why those two SQL processes were monopolizing FAR more memory than they were configured to use, but the primary issue appears to be resolved and the users are happy, so I'm happy as well. 🙂