Memory usage of MSDE keeps growing

Question

Memory usage of MSDE keeps growing

Richard Rice

SSC Eights!

Points: 879
More actions
October 6, 2004 at 6:34 am

#162380

I am getting ready to deploy a manufacturing application that uses a SQL2K MSDE database. It is not a very large database but it gets updates every few seconds, so it is very active.
When I first start the application, the total memory allocated to SQL (as seen in task manager) is about 6M. this is before any transactions stat with the application.
Once the transactions start, the memory usage grows consistantly. After about 12hours of operation, the usage is over 600M (per Task Manager).
My question is, is this normal? Can it be controlled? If so, how? If not, what options do I have to manage this?
It becomes a problem when the PC running the application must start caching resources in order to maintain SQL performance.
Thanks in advance for your help...

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 1

Yes, MSDE is a SQL Server engine so it will continue to request more memory as needed. You can, however, set a maximum amount of memory MSDE can use, much in the same way as with SQL Server. Connect with osql and then issue:

EXEC sp_configure 'show advanced option', '1'

GO

RECONFIGURE

GO

EXEC sp_configure 'max server memory', 'memory in MB'

GO

RECONFIGURE WITH OVERRIDE

GO

The minimum value is 4 (for 4 MB).

K. Brian Kelley
@kbriankelley

Richard Rice SSC Eights! Points: 879 More actions · Answer 2

Thanks, that is exactly whjat I was looking for.

Now, applying these settings is fairly straight forward, but if they start to cause issues, how do I revert back to default settings and start over?

I haven't used this before and do not know how it will hit performance.

btw: I am thinking that setting this to a maximum of 150M should suffice, so the line should be

EXEC sp_cinfigure "max server memory" "150"

go

etc...

is the correct syntax, right?

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 3

The default value is 2147483647.

The best way to handle this is first to issue the sp_configure statement setting advanced options (with RECONFIGURE) to see the current setting, something like this:

EXEC sp_configure 'show advanced option', '1'

GO

RECONFIGURE

GO

Then check to see what the current setting is. To do so, issue the max server memory but don't specify a value (and don't use the RECONFIGURE WITH OVERRIDE).

EXEC sp_configure 'max server memory'

GO

Then you can set the memory to what you want:

EXEC sp_configure 'max server memory', '150'

GO

RECONFIGURE WITH OVERRIDE

GO

And that should do it.

K. Brian Kelley
@kbriankelley

Richard Rice SSC Eights! Points: 879 More actions · Answer 4

Running this now!!!

Thanks for all the help! It is truly appreciated.