Memory Utilization for Multiple Instances of SQL Server

I was recently asked if having multiple instances running on the same machine can cause performance issues. Resource contention can become an issue, though it depends on the utilization of the DBs and the amount of resources on the box.  SQL Server does not allocate memory evenly across its instances.  Each instance will consume whatever memory it needs (even if it's all the memory on the machine) and is stingy when it comes to freeing it back up for other processes to use.

It is usually a good idea to set the max server memory and min server memory for each instance to control memory usage, especially if multiple instances are installed.  Setting the max server memory will ensure that an instance will not take up all the memory on the box.  The min server memory option guarentees that the specified amount of memory will be available for the SQL instance.  Once this min memory usage is allocated, it cannot be freed up until the min server memory setting is reduced.  The following example will set the max server memory to 4 GB.

sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
sp_configure 'max server memory', 4096
GO
RECONFIGURE
GO

These memory settings will take affect without having to restart the instances.