x

Advice on Memory Required

We are running SQL Server 2008 Standard Edition 32-bit on Windows Server 2003 R2 Enterprise Edition 32-bit with 16Gb of memory, of which 11Gb is allocated to SQL Server. The main OLTP database we use has just passed the 600Gb mark and another is 35Gb. Both are replaicted and log shipped to secondary servers.

Are we way under with our memory? If your main database was 600Gb what is the minimum memory you'd want on the server? i.e. to try and keep some of it and tempdb in memory.

NB. We can't up the 11Gb much as we have replication, log shipping running and a few other applications running - we did up it to 12Gb but started to get a few problems.

I think the maximum we can move to is 64Gb but I'm wondering if even this is enough? I welcome any thoughts people have.
more ▼

asked Sep 23, 2011 at 09:12 AM in Default

JohnStaffordDBA gravatar image

JohnStaffordDBA
195 7 8 9

32-bit server cannot use more than 4GB unless I am mistaken.
Sep 23, 2011 at 09:32 AM Shawn_Melton

@Shawn_Melton It can with the switches in the boot.ini. By default a user process cannot take more than half of the 4GB limit, so the process memory is limited to 2GB. This can be bumped by using /3GB switch. Additionally, the /PAE switch will allow Windows to see more than 4 GB. If both switches are enabled then the process can use up to 3 GB and Windows can address more than 4 GB so long as the total installed memory is 16 GB or less. If total memory is greater than 16 GB then only /PAE should be used not both. So, if boot.ini is configured with /PAE (or maybe both /PAE and /3GB) then the configuration can be changed for SQL Server to take advantage of more than 4 gb memory:

sp_configure 'show advanced options', 1;
reconfigure with override;
go

sp_configure 'awe enabled', 1;
reconfigure with override;
go
Of course none of this nonsense is necessary with 64 bit. 600 GB database is probably begging for it. I mean it will certainly get by with just 11 GB on 32 bit, but moving it to 64 bit would be a good choice if at all possible.
Sep 23, 2011 at 09:56 AM Oleg
(comments are locked)
10|1200 characters needed characters left

4 answers: sort voted first

If you take an extreme view on this I could run a 600GB database on my netbook that has an Intel Atom processor and 2GB RAM - if I am the only person accessing the data and I dont write any crazy queries all will be fine. If I want to support an online shopping website for a global brand then I'm going to see problems. The memory you need to run with acceptable performance will be somewhere in between. I would certainly aim for upgrading your OS to 64bit. I have run a CRM system that supported 500 staff (approx 200 concurrent users as average) on a server with 20GB so your 16 may not be under resourced.

Points to address:
are there complaints that the system is slow?
If the system is slow, do your stats point to RAM as the issue? The next suspect would be the storage.

Unless you are churning GB of data between cycles then i dont think replication and Log Shipping will be a big stress. what is the cycle you are running - hourly/daily ?

check for system performance at times the repl and LS run.

At the end of the day more RAM certainly wont hurt and if the hardware is going to be in use for a long time then you may just need it sooner or later.
more ▼

answered Sep 24, 2011 at 08:56 AM

Fatherjack gravatar image

Fatherjack ♦♦
42.4k 75 78 108

Log Shipping runs every minute and is usually at least 1Gb of files!
Sep 26, 2011 at 08:00 AM JohnStaffordDBA
OK, so you need to consider these processes. Still doesnt alter the fact that the DB size doesnt map directly to how busy the server is and what hardware is needed. I'd get down to measuring how it is currently and identify where the greatest stress is and work on improving that part of the h/w. Personally I'd be expecting a first change in OS to 64bit (if you can get faster HDD in same step then great) then RAMs then CPUs. Although if you want a quick fix more RAM may be step 1.
Sep 26, 2011 at 08:19 AM Fatherjack ♦♦
(comments are locked)
10|1200 characters needed characters left

There isn't an equation of how much memory you need based on the size of your database. It is dependent on what your load is on the server. How often is the data being accessed? I have a database that is nearly 1TB in size and had been running just fine on a server with 12 GB of memory. 4 for the OS and 8 for SQL. I have recently moved it to a bigger box 16 core and 64 GB of memory but the move was simply to upgrade hardware and OS from an old 8 year old box. Does the system run better, sure, but that is also because we upgraded from SQL 2000 to 2005 so better query optimization, more ram, better index usage, etc.

You are running SQL 2008, take advantage of the DMO's that related to memory pressure and see if you are hitting any high points, get a good base line of your server to be able to compare good times against bad times. "If you aren't measuring it you can't manage it."
more ▼

answered Sep 23, 2011 at 10:34 AM

Tim gravatar image

Tim
36.4k 35 41 139

+1 for load vs size
Sep 23, 2011 at 11:20 AM Kev Riley ♦♦
(comments are locked)
10|1200 characters needed characters left
I agree with you all, but just want to add another opinion. One Of the mportant factors is, how much memory can you afford? SQL server loves memory.the more memory you have, the more cache is used and the less disk io are performed. Disk io and CPU (licens fees) are expensive, but memory is cheap. Does you largest table fit in the memory? If not, the data will spill over to disk if SQL have to scan the whole table.(trace for sort and hash warnings) Of course, you shouldn't spend too much.
more ▼

answered Sep 23, 2011 at 11:46 AM

Håkan Winther gravatar image

Håkan Winther
15.6k 34 37 48

(comments are locked)
10|1200 characters needed characters left

John It depends on many factors. Best advice is to monitor your system during peak times. Here is a good TechNet article on Memory Bottlenecks and how to monitor. Scroll down to Memory Bottlenecks [http://technet.microsoft.com/en-us/library/cc966540.aspx#EGAA][1]

[1]: http://technet.microsoft.com/en-us/library/cc966540.aspx#EGAA
more ▼

answered Sep 23, 2011 at 10:19 AM

alexsdba gravatar image

alexsdba
221 1 1 4

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x56

asked: Sep 23, 2011 at 09:12 AM

Seen: 1265 times

Last Updated: Sep 23, 2011 at 09:12 AM