Multiple instances and Performance

• ifx

  SSChasing Mays

  Points: 652

  More actions

  July 9, 2004 at 12:05 am

  #161881

  Hi All,

  We have two applications using two different databases, and found that the one application severely affected the other. 

  We also found that the impact can be reduced by running the two applications in two seperate instances. I presume that this is because the one application is a super memory hog, and uses as much of the RAM as it possibly can, leaving very little to the other. Initially however I though that there may be some efficiency gain since each instance will then have its own resources, such as the proc cache etc etc.

  [Softly wimpering: I wish SQL Server had resource groups like Oracle]

  Anyhow, I posted this for two reasons:

  1. I'd like to discuss the advantages and disadvantages of this approach

  2. I'd like to see what you all think about how I could resolve the problem within one instance

  Thanks,

• July 12, 2004 at 8:00 am

  This was removed by the editor as SPAM

• nlion84DBA

  Mr or Mrs. 500

  Points: 562

  More actions

  July 15, 2004 at 11:47 am

  #514716

  This is taken from MSDN:

  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

• Allen Cui-55137

  SSC Guru

  Points: 51650

  More actions

  July 15, 2004 at 12:04 pm

  #514722

  You may have to find out why they affect each other by running in one instance, possible be the TEMPDB?

   

• John Chiu

  SSCrazy

  Points: 2619

  More actions

  July 16, 2004 at 8:46 am

  #514879

  I tend to agree with what Alan stated. SQL server suppose to handle the resource efficiently.You should find the root cause of the slow down.

  I personal really can't find any good reason to run 2 SQL instances for each DB as you stated. SQL kernel consume resources as well. Theoretically, 2 instances may reduce the data cache available to SQL.