Export (0) Print
Expand All

Multiple Instance Recommendations

SQL Server 2000

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

Show:
© 2015 Microsoft