Memory allocation in MS-SQL 2000 instances

[dries]

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 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.

Thanks!
Dries Bessels

[Erland Sommarskog]

dries (dr***@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx