This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(137.6, 40%, 23%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ES%3C/text%3E%3C/svg%3E)
Are there any recommendation on SQL server MULTIPLE instances on the same server : min and max memory setting recommendation ?
min and max memory setting recommendation
The settings depends on the workload per instance, which we don't know and so we no one can give a recommendation.
Memory setting can be change on the fly, so "play" with them to see the effects / performance changes.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(108.80000000000001, 46%, 20%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EAM%3C/text%3E%3C/svg%3E)
Hi sakuraime,
Agree with Olaf. It is better to give each instance memory proportional to its expected workload or database size. You can experiment to find the best settings for your usage pattern. And please make sure the total value of max server memory of all instances does not exceed the total physical memory on your machine. And the sum of min server memory of all instances is 1-2 GB less than the total physical memory on your machine.
Please refer to this doc which might help.
Best Regards,
Amelia
As Olaf and Amelia say, it depends on your workload. Amelia suggests that "total value of max server memory of all instances does not exceed the total physical memory on your machine". This certainly a good idea, since this removes the risk that the instances start fighting with each other. Then again, this also means that you may use less of the memory available. So if the instances are used irregularly, it may be OK to have the total of max server memory to exceed the available RAM.
However, the sum of total min server memory should always be safe below the the total RAM. Obvious? One would think so, but I have experienced a situation where someone had configured two instances on a machine with 64 GB of RAM with 60 GB for min server memory and 62 GB of RAM. This was a performance-test server, and the idea was that only one instance should be running at a time, but at one occasion both instances were started. It was completely hilarious how slow it was!
if the server has 100GB memory with two SQL instance .
instanceA set 70GB max and 50GB min
instanceB Set 80GB max and 50GB min
iwith initially instanceA has many workloads and buffer boom up to 70GB ... later on instanceB has also many workloads , so the total memory of both instance would roughly be 50GB ?
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(67.2, 1%, 16%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ETP%3C/text%3E%3C/svg%3E)
I never recommend changing the min memory option. Setting the min memory on both instances will cause each instance to never use >50GBs after it allocates that much RAM.
Also you need to leave room for the OS memory usage. You should never set Max memory to equal total physical RAM. You need to leave room for the OS and other processes, at least 5-10GBs.
if the server has 100GB memory with two SQL instance .
>
i> InstanceA set 70GB max and 50GB min
instanceB Set 80GB max and 50GB min
Sorry, I should have been clearer. The sum of min server memory should be well be below the total RAM in the machine. The OS needs space to breathe.
I agree with Tom, that min server memory is nothing worth playing with. Although in a scenario like this where you over-provision max server memory (because it makes sense), that it can be a good idea to set min as well, so that that it is not dwarfed entirely. But I would not set it above 20 GB,
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(192, 23%, 28%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EJW%3C/text%3E%3C/svg%3E)
If you do this - do not enable LPIM for either instance. If LPIM is enabled then that instance will not release memory - and you could starve the OS. Much better to make sure the combined max memory settings of all instances is less than the total memory available to the server. If you do have instances where their workloads do not intersect - then maybe you could over-provision, but that is going to take a lot more research on each instances workloads and usage patterns.
thanks all the ideas .
I am thinking if putting multiple instances on the same machine , and also set the min memory , the above states that the draw back is instances will get memory back and forth for each other and lead to instability .
Yes, if you casually put multiple instances on the same machine, you can get into trouble.
But please keep in mind that there is a lot of "it depends" here. We don't know what sort of workload you have in mind. At my client we have a server with four instances on it. I don't recall what memory configuration we have set up, or if we have set something up. But these are all dev/test instances for the same system. On the production server, there is only one single instance.
I think that in many cases I would prefer to have two production instances on two different VM than as two instances on the same VM. But then again, if the VM host is over-provisioned, this is not really different from having multiple instances on the same VM.