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.

Computer/instance	Query type	Execution time (ms)
Single CPU single instance	Select into	420
Single CPU single instance	Select with Group by and Order by	16683
Single CPU single instance	Union query	13590
Single CPU single instance	Join with Group by	4406
Single CPU two instances	Select into	1153
Single CPU two instances	Select with Group by and Order by	24246
Single CPU two instances	Union query	16623
Single CPU two instances	Join with Group by	5076
Two CPU single instance	Select into	314
Two CPU single instance	Select with Group by and Order by	9342
Two CPU single instance	Union query	9972
Two CPU single instance	Join with Group by	1289
Two CPU two instances	Select into	852
Two CPU two instances	Select with Group by and Order by	18120
Two CPU two instances	Union query	12091
Two CPU two instance	Join with Group by	3121