We have a set of SQL Server instances (2008 R2) that run on the same virtual server. Each instance represents a stage in our development cycle (Dev/Test/Stage/etc.). The instances all need to be online at any given time, but load tends to be isolated to one instance as a time, depending on where we are in the release lifecycle.
SQL Server seems to grab and hold whatever amount of memory we set as the max. What I'd like to know if there is any way of configuring the instances to be "smart" about memory consumption so they are not blocking each other from getting memory when needed.
Best Answer
SQL Server will use as much memory as you can throw at it (pretty much), and it is always advisable to set a limit on every instance. The default setting is 2147483647MB.
Depending on your usage and own unique circumstances, your settings may vary, but normally a good starting point is to set the max server memory between 75 and 90 percent, depending on how much RAM you have. Glenn Berry share a good indication a few years back HERE.
If you have multiple active instances on a server, you might have to share the resources between them, for e.g. if you have 32GB of RAM with 3 instances, you should assign around 9500MB to each instance. When you are using just one instance at a time, the others will not release the RAM they are using. You could free up their RAM by stopping the service.
When you have other applications / services running, the amount of memory might have to be adjusted to accommodate them.