SQL Server Instance Memory Pressure

Question

We are moving a couple of our major LoB SQL Servers to a VM cluster and I'm wondering how to best provision the new SQL Servers and SQL Server Instances for best performance and utilization. One idea I had was putting the main OLTP database in one instance with, let's say for example sake, access to all 16 GB of RAM, and another instance on the same server with the same all 16 GB. The second instance would contain a database used for nightly processes wich would need as much of that memory as it can get and isn't needed by the first DB/instance as data is pulled and manipulated within it. Anyways, after the nightly proccessing is done that entire chunk of memory used for instance/dtabase 2 can be freed up for instance/database 1. Now I'm not asking, will it miraculously free up 100% of the memory used by instance 2, but will Windows put memory pressure on the instances and balance the amount used and allocated to each based on need/use?

In case it matters they will be SQL Server 2008 R2 instances on Windows Server 2008 R2 with, again for the example's sake 16 GB of total system RAM available to the VM.

Thanks in advance for any and all input.

Answer 1

When Windows notifies that memory pressure occurs both instances will start trimming, until the memory pressure is lifted (no longer signaled by Windows). In general having to SQL instances compete over same memory is not working. And same goes for CPU, the two instances will compete over the same CPU cycles and nether will be happy.

A much better idea is to host both the OLTP and the nightly processing instances in a single instance, in which case SQL Server can balance out the resources much better. You can even let SQL balance the resources between the two workloads using the Resource Governor.

The only scenario that would warrant separate instances is a very very strong security deterrent that requires the instances to be isolated (ie. the OLTP admins cannot be admins of the nightly process one). In that case I would recommend two separate VMs rather than two instances on a single VM.