Memory allocation in MS-SQL 2000 instances

Hi,
From a previous sys-admin I inherited a a MS-SQL (2000) machine with 3
instances. It is a nice machine with 4 Gb of memory but the memory allocatio
n
is very weird:
Instance A: 1400Mb
Instance B: 1000Mb
Instance C: 80Mb (!)

Instance C is performing badly under a bit of pressure which seems not stran
ge
considering these allocations.

With that in mind, is there a way to check and re-allocate memory? I'd like 
to
see if the instances really need these amounts of memory and if not, to move
some over to other instances.

Thanks!
Dries Bessels

dries  wrote:
> Hi,
> From a previous sys-admin I inherited a a MS-SQL (2000) machine with 3
> instances. It is a nice machine with 4 Gb of memory but the memory allocat
ion
> is very weird:
> Instance A: 1400Mb
> Instance B: 1000Mb
> Instance C: 80Mb (!)
>
> Instance C is performing badly under a bit of pressure which seems not str
ange
> considering these allocations.
>
> With that in mind, is there a way to check and re-allocate memory? I'd lik
e to
> see if the instances really need these amounts of memory and if not, to mo
ve
> some over to other instances.
>
> Thanks!
> Dries Bessels

Could it be that there simply isn't any more memory for the C instance?
You doesn't specify which SQL and OS versions you are running, but there
could be settings that leave e.g. 1 GB for the OS and then you haven't
got any more left for SQL. SQL server will grap memory but doesn't
release it again. This means that if the first two instances already had
grapped the memory then there is nothing left for instance C.
If you have the change, you could try to re-boot the server and then
check if the memory consumption becomes more equal between the three
instances.

Regards
Steen

dries (dries@bessels.stop-spam.org)  writes:
> From a previous sys-admin I inherited a a MS-SQL (2000) machine with 3
> instances. It is a nice machine with 4 Gb of memory but the memory
> allocation is very weird:
> Instance A: 1400Mb
> Instance B: 1000Mb
> Instance C: 80Mb (!)
>
> Instance C is performing badly under a bit of pressure which seems not
> strange considering these allocations.
>
> With that in mind, is there a way to check and re-allocate memory? I'd
> like to see if the instances really need these amounts of memory and if
> not, to move some over to other instances.

You cannot reallocate memory between instances per se, but you configure
per server the max amount of memory an instance can use.

SQL Server's general strategy on memory is to grab as much it needs
and that is available. The use for this memory is cache. The more data
that is in cache, the faster data can be accessed. When there is competition
for memory, SQL Server will yield. In this it appears that the three
instances are competing.

3 instance on 4GB may be a tad low. You are running with the /3GB switch
in window?


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx