300MB DB uses 1.4GB RAM!?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

William Bernat - 25 Oct 2006 20:10 GMT

SQL Server 2000 running nothing but a 300MB database over the course of a
month of continuous use from a single application winds up grabbing 1.4GB of
RAM. Is that normal or excessive? (No performance degradation at all, jut a
lot of RAM allocated.)

This is clustered, but the problem has occurred separately on each node.
Yes, that took over 2 months to test.

-billb

Reply to this Message

Alex Persky - 27 Oct 2006 08:37 GMT

It's going to grab as much as it can and will hardly give it back.

Yes, it's excessive. I would recommend set a limit on max amount of memory
allocation for SQL Server. The default is unlimited (or a very high number).
SQL Enterprise Management Console and right-click the server and go to
properties.

> SQL Server 2000 running nothing but a 300MB database over the course of a
> month of continuous use from a single application winds up grabbing 1.4GB
[quoted text clipped - 7 lines]
>
> -billb

Reply to this Message

Edwin vMierlo - 27 Oct 2006 09:12 GMT

Unless you need to limit it (if you are running other processes on the same
machine), I wouldn't... the more ram SQL takes the better !!
really... you want SQL use all the availabe RAM and cache as much as
possible (in stead of going to disk)

IMHO

> It's going to grab as much as it can and will hardly give it back.
>
[quoted text clipped - 14 lines]
> >
> > -billb

Reply to this Message

Kevin3NF - 27 Oct 2006 13:55 GMT

300 MB database, not GB :)

The question is what is using the other 900MB?

My guess (in another thread on a another group - cross posted) is that
mem-to-leave is large and full.

Signature

Kevin Hill
3NF Consulting
http://www.3nf-inc.com/NewsGroups.htm
http://kevin3nf.blogspot.com

> Unless you need to limit it (if you are running other processes on the
> same
[quoted text clipped - 28 lines]
>> >
>> > -billb

Reply to this Message

Edwin vMierlo - 27 Oct 2006 14:03 GMT

cache for :
queries / views / stored procedures / tables / indexes ... and a lot more
which SQL holds in memory, when it has the RAM available

> 300 MB database, not GB :)
>
[quoted text clipped - 35 lines]
> >> >
> >> > -billb

Reply to this Message

Kevin3NF - 27 Oct 2006 14:31 GMT

Yeah...but the default mem-to-leave is 256mb, right?

Data and index pages go into the Buffer pool.

So, if every page was in memory, there's 300mb. Mem-to-leave would have to
have been set using -g to 900mb...

Odd.

Signature

Kevin Hill
3NF Consulting
http://www.3nf-inc.com/NewsGroups.htm
http://kevin3nf.blogspot.com

> cache for :
> queries / views / stored procedures / tables / indexes ... and a lot more
[quoted text clipped - 41 lines]
>> >> >
>> >> > -billb

Reply to this Message

Edwin vMierlo - 27 Oct 2006 14:39 GMT

http://support.microsoft.com/kb/271624

"DBCC MEMORYSTATUS"

reports back in number of buffers (1 buffer = 8 K)

> Yeah...but the default mem-to-leave is 256mb, right?
>
[quoted text clipped - 50 lines]
> >> >> >
> >> >> > -billb

Reply to this Message

Phillip MacPherson-Cox - 27 Oct 2006 15:54 GMT

Hi,

Have you monitored the sysprocess table to find out what spid is utilizing
most of the memory?

> Yeah...but the default mem-to-leave is 256mb, right?
>
[quoted text clipped - 50 lines]
> >> >> >
> >> >> > -billb

Reply to this Message

Anthony Thomas - 30 Oct 2006 03:51 GMT

MEM TO LEAVE is 384 MB by default (-g is 256 MB default). Don't forget
about the other 128 MB set up to manage the possible default 255 worker
threads at 0.5 MB each. This is added to the -g parameter.

DBCC MEMORYSTATUS will tell you how SQL Server memory manager has
partitioned and allocated the BPool.

Anthony Thomas

> Yeah...but the default mem-to-leave is 256mb, right?
>
[quoted text clipped - 50 lines]
> >> >> >
> >> >> > -billb

Reply to this Message

Alex Persky - 30 Oct 2006 10:19 GMT

Probably internal results from old queries and their indexes. The logic is
why not keeping them, maybe they'll be useful for next queries.

The fact is that SBS, for example, would even rise a warning event that all
available memory has been used by running programs, so I would suggest to
limit it anyway. Influence on speed is not going to be noticeable, until
such time comes when some of the indexes doesn't fit into memory.

> 300 MB database, not GB :)
>
[quoted text clipped - 36 lines]
>>> >
>>> > -billb

Reply to this Message