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.
You should always set your max memory away from default and leave some room for OS (see Jonathan's post of how much Memory to leave based on the amount of RAM installed).
Jonathan Kehayias has blogged about : How much memory does my SQL Server actually need?
reserve 1 GB of RAM for the OS, 1 GB for each 4 GB of RAM installed from 4–16 GB, and then 1 GB for every 8 GB RAM installed above 16 GB RAM.
You can also refer to my answer here for more details.
would it be best to leave the default setting
NO, dont leave it as default as problems like OS unresponsiveness, Working Set trimming as well as other applications running on the server will be affected adversely. It will affect your backups as well.
Note that Memory Manager for SQL Server 2012 and up has changed.
Best Answer
You should absolutely make the most use of the hardware when you are in an optimal config, and adjust when you are in maintenance mode. And yes, you will have an issue while both (or all four?) instances are active on the same node. Since a failover induces a service start on the now-active node, you can adjust the max memory of each server in that event using a startup procedure. I blogged about this here, but for a different reason (failing over to a node with a different amount of memory):
Basically, you just need to check if both instances are on the same node (and this will require a linked server to be set up in both directions), and adjust accordingly. A very quick and completely untested example based on my blog post and assuming there is only one instance on each node at a time presently (the question is a bit ambiguous if you have 2 total instances or 4):
Of course create it again on the other instance, swapping the linked server name used.
This gets a little more complex if you have to adjust depending on whether you are sharing the current node with 1, 2 or 3 other instances.
Note that this will cause other side effects such as clearing the plan cache (in the event when one of the instances didn't just restart or fail over, in which case the plan cache would be empty anyway), but these are arguably better than leaving both instances to assume they still have 12 GB of memory to play with - there will be a lot of thrashing if they're both heavily used.
You may also want to consider other options such as global maxdop, NUMA/CPU affinity etc. depending on how sensitive the system is to the amount of resources available.