-3

I have 60 Gb of RAM on server. Windows 2008, 32bit with PAE.

One Sql Server 2008 Express Instance can use 1 Gb of RAM. So if I install 60 instances, each can use 1 Gb of RAM, right?

And the second question, what will be if I install 64bit system and use many Express instances?

Mark Henderson
  • 68,316
  • 31
  • 175
  • 255
Igor Golodnitsky
  • 287
  • 1
  • 2
  • 12

4 Answers4

28

Oh my... If you have 60Gb of RAM, please save yourself the hassle and use a 64-bit os. Also, 60 SQL instances sounds like a very odd way of doing things and I can't in good conscience suggest it. The amount of money a server of that stature costs, surely a proper SQL license would be in the budget?

Windows is also incredibly intelligent when it comes to memory usage. It will release (page) memory from as required. I've seen SQL Server consume 95% of the avaliable memory in a server, and the other services on the server were sidelined as appropriate.

-- Update --

Judging by your comments, what makes you think that 64-bit windows is eating your RAM? If you're looking at the "Free" reading in the "Physical Memory" group in the Task Manager, this is not an appropriate place to be checking.

-- Update --

Regarding more of your comments below, what you really want to know is: Which is more efficient - 1x40Gb process, or 40x1Gb processes, and this question can't be answered easilly.

It's a bit like this: You need to transport 40 people. Do you buy 40x VW Polo's, or do you buy a 40-seater bus? They both cost about the same. It really depends on what you want to do. Is everyone going to the same place? Do they need to get there at the same speed? Do they need comfort? Do they need safety? Do they hate eachother and are they going to stab eachother in the face at the mere sight of eachother?

Applied to your scenario of SQL server, and a few assumptions (based on other questions you've asked on ServerFault) I can categorically say that you want one massive SQL Server instance consuming as much RAM as it can get its greedy little hands on.

Mark Henderson
  • 68,316
  • 31
  • 175
  • 255
  • 1
    Got to agree with this, any money saved by trying to do this on the cheap will be eaten up by the cost of supporting such a unwieldy platform. 32 bit server with PAE for 60Gb and 60 SQL express instances sounds like a great way to have no end of little problems biting you no matter what you do. – Rob Moir Mar 11 '10 at 20:27
  • 1
    This one should get a +60 if it was possible. – Maximus Minimus Mar 11 '10 at 20:35
  • I'll contribute one of that +60 – squillman Mar 11 '10 at 20:38
  • but 64-bit, eats my needed memory, so 32-bit is more efficient if I have many small applications – Igor Golodnitsky Mar 11 '10 at 20:48
  • 6
    NO. 32-bit is NOT more efficent with more small applications. PAE is not efficient. You cannot tell me that 64-bit uses a substantial amount of RAM. The most RAM I have in a server here is 24Gb, and Windows, idle, after a fresh install, uses 1Gb. That's a totally acceptable overhead, and I doubt it would change if it was x86 or x64. – Mark Henderson Mar 11 '10 at 21:00
  • I've updated my answer to reflect your concerns – Mark Henderson Mar 11 '10 at 21:07
  • Eating - means that 64 bit process uses 1Gb = 32 bit process uses 512 Mb – Igor Golodnitsky Mar 11 '10 at 21:13
  • @dynback.com - still not sure what your concern is? A process will use as much RAM as it needs. Which process are you talking about? Where are you finding this information? On 60gb, 512Mb of RAM should not make a noticable difference to any performance, and will be greatly outweighed by the benefits of not using PAE. – Mark Henderson Mar 11 '10 at 21:25
  • That was sample, the question is - what is more effective 1x40GB-64bit process or 40x1GBx32bit process? – Igor Golodnitsky Mar 11 '10 at 22:11
  • 3
    Effective? Or Efficient? They're two very different things. And the question can't be answered with a generic answer, it can only be answered for specific scenarios. You've asked about SQL server, and there is absolutally no doubt in my mind that 1x40Gb@64-Bit is going to be astronomically ahead of 40x1Gb@32-bit. No contest. Hands down. Not only will it be more efficient, it's going to be a hell of a lot less work to manage, maintain, and connect to. – Mark Henderson Mar 11 '10 at 22:28
  • 3
    Keep in mind that PAE allows the processor to switch which 4GB of RAM is accessable at any given time. There's a performance penalty for switching contexts, so if you are actively using 60GB of RAM PAE is a horrible way to go. – Chris S Mar 12 '10 at 14:56
1

What are you trying to do?

Save money by not paying for SQL Licences?

Host multiple systems?

Running 32bit Windows and 60GB is not going to be very efficient.

Guy
  • 2,658
  • 2
  • 20
  • 24
1

How many processors are on this box? I would be a little worried about how well each SQL Server instance is going to be scheduled. Each instance requires its own memory footprint, its own binaries to be patched, and has to be scheduled by Windows to run.

Also keep in mind that SQL Server Express has a 4gb limit in terms of file sizes.

What kind of plans do you have in place for disaster recovery with a box like this? Also what is the need for 60 instances? Is it strictly to avoid the 1GB RAM limit? SQL will take as much memory as it needs but you can also add more add more than one database to an instances? Is there some security or some other need?

Looking at something like this, I wonder if something like Hyper-V or ESXi wouldn't work better as a solution. You can create as many VMs (not sure you can do 60 but see my previous comments on this) as you need each with one instance of SQL Server Express. This will give you more flexibility if you want to add another server to expand capacity

Brian
  • 31
  • 2
  • this guy gets up points for actually reading the question except that the suggestion of hyper is way worse than just suggesting using a 64-bit OS. – djangofan Mar 12 '10 at 16:43
0

I was curious about this exact point so I did some research with a memory grabber app I wrote. What I found is that yes, PAE on 32-bit will allow more than 4GB of physical memory to be consumed (without page file swapping) by many separate running instances of the same app, each of which has the usual <=2GB limit.

Somewhat covered in the "... multiple processes can immediately benefit ..." point in this article.

Here is a list of articles on the various types memory in general, plus some widely-misunderstood beliefs.

I also tested on 64-bit with my 32-bit grabber, and experienced the same results.

JamesR
  • 1,061
  • 5
  • 6
  • 1
    PAE isnt as good as real 64-bit. with PAE you are still limited in memory size somewhat. i dont remember exactly but you only get like 1GB more memory for a process. – djangofan Mar 12 '10 at 16:42