Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

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)?

share|improve this question

migrated from stackoverflow.com Dec 3 '12 at 16:28

This question came from our site for professional and enthusiast programmers.

    
Just trying to test this at the moment... –  wizzardmr42 Dec 3 '12 at 16:56
    
Have you tried looking at your performance monitors to see what sort of memory usage SQLServer is currently holding? –  Zane Dec 3 '12 at 17:02
    
I didn't actually have 2 instances running - I am setting it up as a test. Problem is that the server is used for other stuff so trying to get it to hog the memory during working hours is not so sensible, especially as this isn't the server that I want to eventually set it up on - this one only has 2GB RAM! –  wizzardmr42 Dec 3 '12 at 17:04

3 Answers 3

up vote 16 down vote accepted

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.

BOL extract

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.

share|improve this answer
2  
Great compositing of the answer there, using all the resources determined to-date to be useful in this situation. –  jcolebrand Dec 3 '12 at 17:17
2  
Also of note, for the astute reader: SQL Server Database Engine means A Single Instance. Everytime you have to change the connection dialog server that you're connecting to (eg localhost\dev vs localhost\test) that's a new "Instance" or a new "Engine". Just thought I would clear that up. –  jcolebrand Dec 3 '12 at 17:20
1  
Just want to say thanks - you've really gone to a lot of effort to resolve this! The databases are totally independent and it makes a lot of sense to use separate instances anyway - I'm running a shared hosting server for several clients that I do development work for, so a little more isolation won't do any harm from a security or reliability point of view –  wizzardmr42 Dec 3 '12 at 20:20
1  
@wizzardmr42: right, as long as (several <= 16) –  ypercube Dec 4 '12 at 7:01
1  
@RichardTheKiwi I take no credit for the torn page graphic, that's all down to the SnagIt screen capture utility by TechSmith. –  Paul White Dec 4 '12 at 9:28

Every Instance counts separate as it has a separate process.

share|improve this answer
    
I assume that you are certain that the limiter isn't applied across all processes then? –  wizzardmr42 Dec 3 '12 at 14:27

In SQL Express, the database engine can only consume 1 GB RAM, and any DB cannot be larger than 10 GB.

So, 4 instances would still have to share that 1 GB RAM.

You can find out much more at http://msdn.microsoft.com/en-us/library/cc645993(v=SQL.110).aspx.

A similar discussion here on Stack Overflow says this is the right answer BTW, so no need to downvote: Limitations of SQL Server Express

This page says the same, even though it is about SQL Server Express 2005: http://www.edugeek.net/forums/windows-server-2000-2003/55183-sql-server-express-2005-memory-limit-per-instance.html

share|improve this answer
2  
Doesn't necessarily follow - is it 1 engine per server or per instance? –  wizzardmr42 Dec 3 '12 at 14:28
1  
OK, that does imply it, but it is also possible that they missed it out on the memory limit line. –  wizzardmr42 Dec 3 '12 at 15:19
1  
Just because it's on the internet, it has to be true. They would never let something wrong be on the internet. ~ Saying that to say that the issue here is that there has to be a management process or something baked into the OS for the sole purpose of restricting all SQL Express forever. I really don't think that has happened (AKA it absolutely didn't happen, based on the fact that I breathe oxygen and can follow logic really well) so the fact of the matter is that the process itself manages the amount of RAM being used. –  jcolebrand Dec 3 '12 at 15:37
1  
Unless, of course, there's a service process that has the explicit task of managing the memory consumed by all SQL Express instances, which I've never seen or heard about. –  jcolebrand Dec 3 '12 at 15:37
2  
Maximum memory utilized (SQL Server Database Engine) Notice how they use the same terminology there as they do on the line: Maximum Compute Capacity Used by a Single Instance (SQL Server Database Engine)1 (for those playing at home, the point of that is that they are the same measurement) msdn.microsoft.com/en-us/library/… and I also love how the answer on SO that was linked to is WRONG WRONG WRONG. Start with "1 core or 4 sockets", the editors can't even copy-paste correctly. –  jcolebrand Dec 3 '12 at 16:03

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.