Is there any way I can make garbage collection aggressive in SQL 2000 or 2005?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Nimesh - 22 Aug 2005 11:52 GMT

Please let me know if there is any way to make to this possible.
And another issue we are facing is that, SQL 2000 is consuming a lot of
memory and not releasing it. Is there wany to make this SQL 2000 to
release the memory when it does not require.

Reply to this Message

Paul - 22 Aug 2005 11:55 GMT

> Please let me know if there is any way to make to this possible.
> And another issue we are facing is that, SQL 2000 is consuming a lot of
> memory and not releasing it. Is there wany to make this SQL 2000 to
> release the memory when it does not require.

I think this behaviour is by design.

Databases work well when a lot of the data is in memory (because memory
access is faster than disk access). So SQL Server and other DBMSs will
try to use as much of the available memory as they can. So if you have a
2GB database it will potentially try to use something in the order of
2GB of memory.

If they share a machine with other applications, I think they will
release memory when other applications require it.

What other applications do you have running on the same machine?

You might also want to check in a SQL Server-specific newsgroup such as
microsoft.public.sqlserver.* or comp.databases.ms-sqlserver for more
specific information.

Paul.

Reply to this Message

Nimesh - 23 Aug 2005 06:18 GMT

Thanks for your reply. I know it is behavior of SQL 2000, and it is
supposed to release the memory when not required. That is why I want to
make garbagge collection aggressive. Other than this we are also facing
this problem in Analysis server, this service is consuming more than
1.5 GB of memory. And while the cube in the process mode, it becomes
very very slow to get the data. We are also trying to optimize the
cube. How to Optimize the cube in SQL 2005?
But my primary query is, is there any way by which we can make garbage
collection aggressive in SQL 2000. Please let me know if any one knowa
how to do it?

Reply to this Message

mountain man - 23 Aug 2005 07:29 GMT

> Thanks for your reply. I know it is behavior of SQL 2000, and it is
> supposed to release the memory when not required. That is why I want to
[quoted text clipped - 6 lines]
> collection aggressive in SQL 2000. Please let me know if any one knowa
> how to do it?

If it is a matter of survival I'd be asking
the following questions.

1) Is your machine operational 24x7x365, and if it is not
2) Have you examined the memory released at re-boot?
3) Have you assessed the rate of its accumulation after reboot?

Signature

Pete Brown
IT Managers & Engineers
Falls Creek
Australia
www.mountainman.com.au/software

Reply to this Message

Nimesh - 23 Aug 2005 08:00 GMT

It is not very urgent but we need to find out the cause and solve if
possible:
1. Yes, this machine is operational 24*7*365. But we do reboot this
server once in week (Every sunday morning) for backup and to clear
memory.
2. Yes, we have examined the memory release. And it does get release
after re-boot.
3. Yes, we have accessed the rate of accumulation after reboot and
re-start of the service. After reboot/restart the Analysis service
cosumes 150 MB and after a day 400 MB, and after a week if the service
is not restarted it reaches at round 1.5 GB. Do not know why this
happening. We want to findout the cause of this. And possible solution.

Reply to this Message

mountain man - 23 Aug 2005 10:42 GMT

> is not restarted it reaches at round 1.5 GB. Do not know why this
> happening. We want to findout the cause of this.

Have you had a look at the performance monitoring
of the memory by using the peformance monitoring
tool available within SQL server?

There are 29 different parameters associated with
the memory management available for monitoring.

If you go through them you will probably find more
information about the specific problem you are
experiencing.

You can run the perfomance monitor for all these
29 different memory parameters in the background
for a test period.

The cause (or causes --- multiple problems are common
enough) should be narrowed by this review.

> And possible solution.

Will depend upon the cause(s).

Look at the exercise as a series of steps,
the initial one being hunting and gathering
of information associated with your
machine memory management.

Good luck.

Signature

Pete Brown
Falls Creek
OZ
www.mountainman.com.au

Reply to this Message