Reducing memory usage - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Reducing memory usage

Author

Topic

drumm
Starting Member

14 Posts

Posted - 2003-05-05 : 04:20:50

Hi,

I'm running an SQL Server 2000 SP3 with 16 user databases attached. 6 of these are currently being used. My SQL Server is using about 315 MB RAM, which I think is a lot considering that the average size of the databases is 10 MB. The most workload that the server experiences is a connection to a database once every 4-5 seconds.

I've recently dropped a 350 MB database from the server after backing it up. Before dropping it, the memory usage was the same. When I create a database with the same name, it automatically grows the database to the 350 MB size, even though it only uses 2% of that.

Is it normal for the SQL Server to use so much RAM? Does anyone have any suggestions to what I can try?

Thanks in advance!

franco
Constraint Violating Yak Guru

255 Posts

Posted - 2003-05-05 : 05:20:25

You can manage how much memory SQl server use thru the "max server memory option" parameter.

Max server memory is an advanced option, so in order to modify this parameter you will need to first do this commands:

USE master
EXEC sp_configure 'show advanced option', '1'

RECONFIGURE
EXEC sp_configure

and after that you are ready to change it thru this command:

USE master
EXEC sp_configure 'max server memory', 'YOUR AMOUNT IN MB'
RECONFIGURE WITH OVERRIDE

Remember to run the first command again but this time with a value of zero.

USE master
EXEC sp_configure 'show advanced option', '0'

RECONFIGURE
EXEC sp_configure

HTH

Franco

nr
SQLTeam MVY

12543 Posts

Posted - 2003-05-05 : 10:42:20

Is it causing a problem?
sql server will grab any available memory that it needs - and usually will run faster with more memory so is a good thing.

For the database - try creating it with a specific size. Also make sure the old file is not still on disk.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-05-05 : 12:32:46

quote:

I've recently dropped a 350 MB database from the server after backing it up. Before dropping it, the memory usage was the same. When I create a database with the same name, it automatically grows the database to the 350 MB size, even though it only uses 2% of that.

Check the size of the model database. If it 350MB, then there is your problem. The default size of the databases come from model.

quote:

Is it normal for the SQL Server to use so much RAM? Does anyone have any suggestions to what I can try?

Yes it is normal. You should not change the "max server memory" option unless other applications or the OS is affected by SQL Server consuming so much memory. If you take away memory from SQL Server, then SQL Server will possibly not perform as well as it did. On a server that is dedicated to SQL Server, SQL Server should be given all of the memory minus about 128MB. 128MB is what is recommended for the OS to use.

Tara

drumm
Starting Member

14 Posts

Posted - 2003-05-06 : 04:35:54

Thanks for the info, guys!

Just after I posted, I saw the SQL Server release about 150 MB of RAM in a matter of minutes (I wonder if it reads the forums as well? :) Now, it's steadily allocating it again. Does anyone know if SQL Agent jobs run a short intervals (once every minute) take up memory that it doesn't release again afterwards?

Oh, and the model database is only 640 KB large, so that's not the problem with the 350 MB database.

I'm going to push some more for that extra RAM module (which is the only reason I worry about this anyway :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources