SQL Server Performance

multi instance sql server 2012

Discussion in 'ALL SQL SERVER QUESTIONS' started by timscronin, Jun 5, 2013.

  1. timscronin New Member

    I have a big box with sql 2012 standard edition. 128 gig of ram. Since there is a 64 gig memory limit on 2012 std edition, I was thinking of firing up 2 instances (this is an active/passive cluster). and assigning 64 gig to each instance. The server is licensed per proc so for the license side I think I am ok, from a technical perspective does anyone see an issue?
  2. davidfarr Member

    If you do decide to go with 2 instances, then it will of course not be an exact 64GB / 64GB split, but probably more like 60/60 at the most, to allow the OS at least 8GB for itself and for other processes.

    You might want to just install one instance to start with, with dynamic memory, and observe how much RAM a single instance is actually using when running some heavy test queries.
    I know of very few SQL server instances that actually fully use more than 64GB RAM for query processing and caching, even on large databases.
    Unless you can show that a single instance is actually using and requiring the full 64GB, then having 2 instances will offer you no performance gain, in my opinion.

    You mentioned a cluster scenario. If your purpose behind the second instance is merely to serve as a passive failover node for another server, then that's a different story and my comments above would not apply.
  3. timscronin New Member

    Actually the idea of the multi instance is we have a 256Gig box and Standard Edition SQl Server
  4. Normally anywhere between 75 -80% of the server memory is allocated to sql server and rest is left to OS and any other applications running on the server. the calculations many change based on the requirement. Based on this you can do the calculation and distribute the memory between the sql instances as per the usage requirement.

Share This Page