10

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.

Improve this question
2
  • If all the instances needs to be online at any give time, and you set the max memory, SQL Server wont release it. When you say .. load is isolated to one instance as a time, does it mean there is no activity on other instances ? Also, how many instances you are running on a virtual and what is the CPU / Memory configuration for the virtual ?
    – Kin Shah
    Apr 19, 2013 at 15:29
  • 3
    I wrote about this for multi-instance clusters, where you need to adjust memory in the event of a failover that leads to multiple instances on the same node. You could use a similar approach here, but instead execute stored procedures on demand that reconfigure the memory based on the current need. You won't be able to set each instance to do this automatically unless you simply fix them each to a max memory permanently (and then adjust manually when you need to).
    – Aaron Bertrand
    Apr 19, 2013 at 15:43

1 Answer 1

Reset to default
8

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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.