Maximum Memory per instance using Developer Edition SQL Server 200

Question

Maximum Memory per instance using Developer Edition SQL Server 200

abeljda

SSChasing Mays

Points: 625
More actions
May 1, 2006 at 1:11 pm

#111937

Hello, we are currently running 8 instances on a test machine, using SQL Sever Developer edition for each instance. Is there any reason besides a physical or OS limitation as to why we could not put 16 or more gig of ram in this machine and then allocate a fixed amount to each instance? We are currently allocating the existing 2 gig over the 8 instances and the performance is terrible. Thanks!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Andrew Stanton Ten Centuries Points: 1115 More actions · Answer 1

Are you using VM's? You may be able to configure memory management through the host's parasite(virtual machine) manager.

You should put performance counters on the server and see if its really memory thats your bottleneck, 8 instances of anything is usually 7 too many.

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 2

Sounds a very sensible solution to me.

If you are using multiple sql instances ( not vm or virtual servers ) then the memory for each instance, including default, should always be fixed, if you allow any dynamic memory you will suffer badly. With 2gb and 8 instances you should allocate 200mb to each instance as fixed memory. ( hence your desire to use 16gb I guess )

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/

Antares686 SSC Guru Points: 125444 More actions · Answer 3

What OS are you running BTW? Windows XP will limit you to 4GB of which 2GB can be used by SQL Instances. So yes your OS can impact what you can do. Also, if your OS can support more than 4 GB of memory depending on the amount you may have to add a couple of switches to your boot.ini file to use as much as possible. Lastly you may even have to AWE mode to work with any memory above 4GB with any SQL instance.

And further considerations from BOL "awe enabled Option"

Usage Considerations

Before enabling AWE, consider the following:

When awe enabled is set to 1, instances of SQL Server 2000 do not dynamically manage the size of the address space. SQL Server will reserve and lock almost all available memory (or the value of max server memory if the option has been set) when the server is started. It is strongly recommended that you set a value for the max server memory option each time you enable AWE. Otherwise other applications or instances of SQL Server 2000 will have less than 128 MB of physical memory in which to run.
If the total available memory is less than 3 GB, the instance of SQL Server 2000 will be started in non-AWE mode even if awe enabled is set to 1. In this situation, you do not need to manage AWE memory because dynamic memory allocation is used automatically.
You can determine the amount of memory you can safely allocate to instances of SQL Server 2000 by identifying how much memory is available after all other applications to be used on the computer have been started.
Use the SQL Server Performance Monitor Total Server Memory (KB) counter to determine how much memory is allocated by the instance of SQL Server running in AWE mode. Configure the max server memory option to leave some additional memory free to allow for the varying needs of other applications and Windows 2000. For more information, see Monitoring Memory Usage.

Important Using the awe enabled option and the max server memory setting can have a performance impact on other applications or on SQL Server running in a multi-instance or cluster environment. For more information about using AWE memory, see Managing AWE Memory.

abeljda SSChasing Mays Points: 625 More actions · Answer 4

You folks gave me the info I was needing, sorry I did not reply sooner, our email filter has been blocking this bits of useful information. Thank you for your replies.