Dynamically Adjusting max server memory Between Two Instances

Question

I’ve got one server with two SQL instances. The server has 128 GB RAM.

• Instance 1 has peak load from 08:00–10:00

• Instance 2 has peak load from 14:00–17:00

Currently:

• Instance 1 has max server memory = 70 GB

• Instance 2 has max server memory = 50 GB

• The rest of the day, both are nearly idle.

Would it make sense to dynamically shift memory? give more to one instance during its peak while reducing it on the other? Any downsides to doing that with a scheduled script?

• stacylaray: Do you see any lazy writes for either instance during their peak times? Are you experiencing any performance issues during those peak times?

Answer 1

It would make better sense to add more memory to the server and raise the max server memory setting for both instances, in my opinion. Memory isn't expensive.

A downside to doing that with a scheduled script is that adjusting Max Server Memory will clear your plan cache which can be a performance hit as it will have to compile query plans all over again.

Answer 2

The optimal way is to use a single instance on the server and give it all the memory. Then it will manage memory across all the databases automatically.