gary1 (9/12/2013)
In which scenarios, sql server instance will release it's memory other than it's service restarts?
When windows is under memory pressure and requests SQL to reduce its memory usage.
Do we need to set Min memory if we have multiple instances?
No, not unless you're overcomitting memory (setting max server memory high enough that all instances cannot reach it given physical memory and what else is running)
Can you please shed some light on this Min memory setting? i.e which scenarios we need to set this?
The only time you really need it is when there's not enough memory on the server for everything running and for all the instances to reach max server memory and hence you want to prevent SQL from reducing its memory to nothing when Windows hits memory pressure. Of course, what's instead going to happen under severe memory pressure is either the OS pages SQL out or the OS crashes due to insufficient memory.
It's also useful in a multi-instance cluster when there's a chance that all instances will end up on one node and that node does not have enough memory for all the instances to reach their configured max server memory. That's usually a temporary situation, so it's there to make the best of what the server has, not for long-term configuration.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability