Memory Usage

[osuman]

We have a system that uses SQL Server 2000 Standard Edition.

It runs on a single processor system with 1GB of RAM.

As a heavy load gets placed our service, it starts to perform poorly. We believe it is because SQL Server has allocated a large amount of memory to itself (300 MB).

Obviously, one solution would be to increase the memory in the system. However, I have read that I can limit it's memory usage using the sp_configure table in the master DB.

What are the negative effects of limiting it?

 

[mrdenny]

The more memory SQL Server has the better it will perform.

SQL Server caches lots of data into memory for faster access. If you limit the amount of memory that SQL can use, it will be caching less data into memory, and will run slower.

Your best bet would be more ram. I'd recomend another 1.5 GB as SQL Standard will only use 2 GB. That will leave .5 GB dedicated to your OS. (Put in more if you need more for other apps on the server. Win 2003 Standard will take 4 GB, Win 2000 Standard will only take 2 GB.)

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[Catadmin]

In addition to adding the RAM, you might also consider another processor. Run a SQL Profiler trace and/or a Windows Event Trace to see what the server is truly having problems with. It could be anything from the memory to the processor to the need for more disk space...

SQL Server seems to work better if you allow it to dynamically allocate its memory. If you put a max and min on it, as Denny said, it can end up making things even slower. You can, however, uncheck the "Boost SQL Server Priority on Windows" in the Server -> Properties -> Processor tab and see if that helps you any. This might also cause SQL slowness issues, but trying this out for a day or two is the cheap-n-fast way of doublechecking yourself before you spend money.

Also, consider what other programs you have running on the server. Exchange & SQL Server don't get along on the same machine very well. And someone told me that neither does SQL Server and the AD Global Catalog (not sure if this is true). If you're running a heavily used Print Server off this machine, or another program (such as the above), you might consider separating them out on individual machines to ease your network burden.



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[osuman]

Thanks for the posts.

I guess I should have been a little more specific. Our s/w doesn't really use SQL as a primary system. It's mostly a place to keep config settings and log data.

Most of the work is being done by our process for telephony applications. So, I don't really care if SQL runs slow as long as I can read config values from it and write log information.

The biggest thing I don't want it doing is eating up so much memory that our process can't run efficiently. This has much worse effects from a user stand-point. Ultimately, though our process does depend on SQL at varios stages in the code.

In this scenario do you see limiting it's memory usage as a viable alternative?

 

[mrdenny]

Yes I would see limiting it's memory usage as a good option. Use sp_configure to set the max server memory option to the max number of megs that you want SQL Server to use. This is the code that you will need. max server memory is an advanced option, so you will need to enable advanced options first.

exec sp_configure 'show advanced options', 1
go
reconfigure
go
exec sp_configure 'max server memory', 256 /*This is in Megs*/
go
reconfigure WITH OVERRIDE
go
exec sp_configure 'show advanced options', 0
go
reconfigure
go

Adjust the 256 to what ever setting you see as a good setting for you. I'd recommend not going below 192 MB. This setting should take effect right away with no restart needed.

If the SQL Server doesn't release the memory for some reason, restart the service. A full server restart shouldn't be nessary. Be sure to restart the SQL Agent (if it's normally running) after the SQL Server restarts.

Your SQL Server might benifit from some basic database maintaince. If you are interested in that let me know, and I'll ask some questions to that and walk you through it.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[osuman]

Thanks again. I will give it a try.

Couldn't I just change the value using Enterprise Manager instead of running the query you posted??

 

[Catadmin]

Osuman,

Yes, you can just change the value in EM. It will probably tell you that you need to stop & restart the service when you do, though, so only change it when it won't matter that the SQL Server is offline for several minutes. Also, as Denny said, verify that SQL Server Agent restarts again (if you have it running) after the SQL Server Service restarts itself.

I've seen a lot of jobs fail because SQL Service got restarted, but someone forgot to turn the Agent back on. @=)



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[mrdenny]

Yes you can change it in EM using the slide bars, but they aren't very precise. The code will give you more control over what you are setting the memory to.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[osuman]

Slide bars???

I just meant going the the sp_configure table and changing the value directly in the table.

 

[mrdenny]

There is no table called sp_configure. sp_configure is a system stored procedure.

You should never attempt to edit system tables directly. Doing so can have unexpected and unplesent results.

There is a reason that all these system stored procedures exist to do these functions.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[Catadmin]

OY! Denny is right. NEVER EVER edit a system table directly or you could kill your server. BADBADBAD...

The only way to change the value without code is to go into EM and follow the directions I gave you. If, like Denny said, you want to be more precise, use his T-SQL code in Query Analyzer.



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[osuman]

Didn't realize it was so bad! I guess it wasn't sp_configure, but at one point I found the table where it was defined.

I'll just use the slider bar when it's a good time to restart the machine.

Thank you both for all your help!