Multiple Instance Recommendations

Before installing multiple instances of Microsoft® SQL Server™ 2000 on the computer, you should be aware of the resources each instance will be using. Each instance acts like an individual server and yields resources only to the operating system and not to other instances. For example, if instance1 needs more memory to run a query, it will not ask instance2 to yield but will request a memory grant from the operating system.

If you have multiple instances installed on a single-CPU computer, with both instances actively processing queries, expect a slowdown in the queries because both instances will compete for CPU resources. In that environment, a query that is resource intensive, such as one containing JOIN with GROUP BY or ORDER BY clauses, may take twice as much time to run as the same query on a single instance installed on a single-CPU computer. This information is based on comparing the query execution on a single-CPU computer with one instance to two instances on the same computer, with both instances running the same CPU intensive operation simultaneously.

Installing multiple instances on a computer with low RAM leads to slower query execution. For example, installing three instances on a server with 64MB of RAM will slow your queries significantly. You can expect that about 15 percent more time will be required to run the same query.

Running Multiple Instances

Consider switching to a "Fixed memory size" configuration for server memory. This configuration will prevent one instance from taking all available memory. For example, you might want to assign 80 percent of the RAM to the production server, and 10 percent to the development instance.

Consider assigning CPUs to a specific instance using the affinity mask option on an SMP computer. For more information, see Allocating Threads to a CPU.

Some Sample Test Results (Averages)

The following figures are from ad-hoc testing. Your results might be different.