Is 2GB physical Memory enough for SQL Server 2008 RTM on a Virtual Machine?

Question

Is 2GB physical Memory enough for SQL Server 2008 RTM on a Virtual Machine?

Mile Higher Than Sea Level

SSCarpal Tunnel

Points: 4828
More actions
February 24, 2012 at 3:32 pm

#391650

Microsoft recommends 2 GB, however other articles discussing VM talk about a high page fault rate if memory is surpassed.
This is a real world question.
Is 2 GB for a small SQL Server 2008 database (assume 100 M of data) running on a VM enough?
Thank you

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 1

Someone with more knowledge in this area will likely correct me but I'd say that "it depends" on how many sessions are going to be hitting this box at once.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

sengeet86 Ten Centuries Points: 1252 More actions · Answer 2

I think,this much memory is enough but Its also depends on what things you will do with sql server.

java programming

Revenant SSC-Forever Points: 42467 More actions · Answer 3

It will depend on the edition: generally, Express expects and uses less memory than DataCenter.

Also, it will neatly tie into service levels -- if you do not have enough memory, don't promise to handle high transaction rates; vs. having Excel users who are loading data into their pivot tables while they went to get their cup of coffee.

As a rule of thumb, in 2012 for SQLS 2008 R2, I expect to have an 8 GB box.

Mile Higher Than Sea Level SSCarpal Tunnel Points: 4828 More actions · Answer 4

Just a reminder, this is on a Virtual Machine. Plenty of Hard Drive. But, only 2GB RAM allocated.

I am running a very small simple View that returns 5,000 records as a single user.

The Perf Mon shows hundreds of memory page faults as 60% or more memory is used.

Microsoft recommends 2GB for the RTF.

I agree that 8 GB on my portable is a bare minimum.

It would seem that 4G on a VM might be necessare just for the overhead of the VM swapping.

Eventually, 30 users creating Excel pivot tables for data mining will be added.

My concern is that a single View running from SQL Manager is taking seconds.

Just trying to figuer out why Microsoft would *recommend 2GB* in almost all cases for a VM.

So far, I am enouraged with the responses.

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 5

Mile Higher Than Sea Level (2/26/2012)
...just for the overhead of the VM swapping

It's been a while since I've actually worked with VM of any kind so it might no longer be true but, in the past, I've found that, although VM isn't bad for SQL Server, you might want to turn of VM swapping. SQL Server seems to like "stable" resources.. a lot.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Nadrek SSC-Insane Points: 20039 More actions · Answer 6

I agree with what Jeff said - if you want the VM to perform consistently, give it dedicated resources (i.e. set the minimum CPU to a reasonably high level and give it a lot of shares for the difference between minimum and maximum CPU allocation, set the minimum RAM to equal the maximum RAM, disable memory ballooning, etc.)

As for 2GB being enough for a 100MB database, what else is that VM doing, and does it really have that 2GB all the time? If possible, experiment! It's a VM; give it 1.5GB, see what happens. Give it 2.5GB, see what happens. Put a meeting request on your schedule to retest in a year.

EdVassie SSC Guru Points: 60472 More actions · Answer 7

What is the Business SLA? If the performance satisfies the SLA, then the box is good enough.

If the SLA is met, then your next question should be 'Is the box over-configured - can any memory or CPU be taken away for use elsewhere?'

If the SLA is not met, then you need to troubleshoot to find the bottleneck. Blindly adding more memory won't help you if the bottleneck is CPU or network.

If you do not have a SLA, then make one up based on the current configuration. If it is accepted then this job is done and you can move on to the next problem.

Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

Mile Higher Than Sea Level SSCarpal Tunnel Points: 4828 More actions · Answer 8

[Solved]

Thanks. The IT her doesn't really have SLA.

They did agree to bump it up from 2GB to 4GB today.

After running the same exact test, will be able to go from there.

Thanks for the additional information of setting minimum to the maximum.

Will look into the other things.

Any other suggestions would be very appreciated.

Added:

The 4GB made a huge difference but not enough. However, the mem page fault went away and exposed the next problem. It did show that the CPU % time was still too high - averaging 95% just for idle.

IT added a 2nd uProcessor to the VM. Now the CPU at idle is 3% and an agressive execution of a view 4 times a second doesn't peg any thing.

My Access linked table to the same view is very fast.

Microsoft for the VM setup of SQL Server 2008 should "recommend" 4 GB of RAM and 2 uProcessors. Good IT workers followed the MS Recommendations.

Thank you everybody for your suggestions.