SQL Server – Fixing Memory Issues with Failing Queries

If I have 8GB of RAM in a server and I run 4 instances of SQL Express, will the total memory limit used by SQL Server be 1GB or 4GB?

Each instance can use up to 1GB of memory for the buffer pool. Each instance can use a bit more than 1GB in total because not all memory allocations go via the buffer pool. In your case, the maximum memory used by the four instances for buffer pool would be 4GB.

To confirm, I started two instances of the SQL Server 2008 Express Database Engine, performed some activity to load up the (separate) buffer pools, and then looked at per-instance memory utilization in a number of ways, for example using DBCC MEMORYSTATUS or by counting the number of buffers using the sys.dm_os_buffer_descriptors DMV.

The physical memory usage numbers below were obtained using simultaneous queries against the system DMV sys.dm_os_process_memory on each instance of the database engine:

SELECT 
    dopm.physical_memory_in_use_kb 
FROM sys.dm_os_process_memory AS dopm;

Output:

╔═══════════╦═══════════╗
║ Instance1 ║ Instance2 ║
╠═══════════╬═══════════╣
║   1102872 ║   1059812 ║
╚═══════════╩═══════════╝

Each of these is slightly in excess of 1GB as total physical memory usage includes more than just buffer pool usage, as mentioned previously.

Would it be advisable to run multiple instances like this to enable each database to make better use of resources (assuming that the server has plenty of resources)?

If the databases on each instance are functionally independent then it is at least workable to run multiple instances of Express in this way, though you would need to pay careful attention to configuration and maintenance may be made more complex.

You might be be better served using another edition, such as the fully-featured (and very cheap) Developer Edition, if the intended usage matches the licence). You would need to say much more about the circumstances to get a clear answer on this point.

I would suggest you to leave MIN Server memory to DEFAULT.

Min server memory controls the minimum amount of Physical memory that sql server will try to keep committed. When the SQL Server service starts, it does not acquire all the memory configured in Min Server Memory but instead starts with only the minimum required, growing as necessary. Once memory usage has increased beyond the Min Server Memory setting, SQL Server won’t release any memory below that amount.

Bob Dorr explains this settings as :

Min Server Memory

Use the min server memory setting with care. This is a floor to SQL Server. Once committed memory to reach the min server memory setting SQL Server won't release memory below the mark. If you set max server memory to 59GB and min server memory to 56GB, but the server needs to back SQL Server down to 53GB SQL Server won't drop below 56GB. When you combine this setting with locked pages in memory the memory can't be paged. This can lead to unwanted performance behaviors and allocation failures.

Searching the web -- internet has plenty of information for max server memory

This is because, this settings is least tuned (people just set it to default), instead max memory is what is generally tuned as it is the "ceiling" for the buffer pool. A good value for max memory will ensure that windows and other processes runing on the server will have enough physical memory to perform their work without forcing sql server to trim.