MS SQL Server - Sql 2000 Performance

Author

Sql 2000 Performance

nospam-google.com@zairgit.com

2005-11-29, 9:23 am

I have a box with 3 instances of SQL on it. One of them jumps up to
1.7 gigs of memory use after it runs for a while. It runs steady at
that point. I have a number of developers and apps that use that DB,
and of course they all deny that it's them. Unfortunately, when I go
into EM > Management > Current Activity > Process info, none of the
processes has higher than 113 under memory usage. In fact it only
shows 2 tranactions being open, and they correspond to the account I am
logged into for EM. I updated the instance to SP4 last night and
rebooted (it was SP3), but the same thing today.

How else can I isolate the problem?

Thanks.

Greg Linwood

2005-11-29, 1:23 pm

This is not a problem at all - SQL Server is simply doing what it's supposed
to be doing.

Database systems always attempt to optimise performance by caching data
requests in memory, to try & avoid disk i/o (SQL Server, Oracle & DB2 are
all the same with this). For example, when a query is run, the data it
accesses from disk is left in the cache so that the next time a similar
query is run by another user, the data can be retrieved from memory rather
than disk.

Hence, it is natural for a RDBMS such as SQL Server to want to use as much
memory as possible on a machine to maximise this data caching behaviour.

The memory growth you're seeing probably relates mainly to this expected
data cachning behaviour, but another factor is that you're using multiple
instances. Although you *can* install multiple instances of SQL Server, it's
not always a great idea to do so on a machine with limited memory, because
each instance will try to use as much memory as possible for data caching,
leading to significant memory resource contention. The way to manage this is
to set the Max Server Memory property, either via the Enterprise Manager or
the sp_configure command, so that each instance can only use a maximum
amount of memory. It would also help to simply install more memory for the
three instances to share.

HTH

Regards,
Greg Linwood
SQL Server MVP

<nospam-google.com@zairgit.com> wrote in message
news:1133276249.239371.108190@f14g2000cwb.googlegroups.com...
>I have a box with 3 instances of SQL on it. One of them jumps up to
> 1.7 gigs of memory use after it runs for a while. It runs steady at
> that point. I have a number of developers and apps that use that DB,
> and of course they all deny that it's them. Unfortunately, when I go
> into EM > Management > Current Activity > Process info, none of the
> processes has higher than 113 under memory usage. In fact it only
> shows 2 tranactions being open, and they correspond to the account I am
> logged into for EM. I updated the instance to SP4 last night and
> rebooted (it was SP3), but the same thing today.
>
> How else can I isolate the problem?
>
> Thanks.
>

Sponsored Links