x

How Much Memory Should I use with SQL 2008 R2

Hey all,

I have a best practices question for you all that is based totally on experience.

I just got a new 2008 R2 SQL Server, running on 2008 R2 Windows Server. I have 4 quad zeon processesors (total of 16 logical processors) and only 8GB of memory in it. (I know.. I see the bottleneck too)

Now, it would not be uncommon for me to have about 50 users or so, logged into the ReportingServices (Same Machine) pulling reports. Plus some general data type functions.

What would be an appropriate amount of memory for this box.. As it stands, there is only about 512 MB of memory per logical processor, which means theres literally no room to do anything quickly after the windows server 2008r2 overhead.

Thanks for your thoughts.

~Dan
more ▼

asked Jun 21, 2011 at 11:15 AM in Default

dregalia gravatar image

dregalia
11 1 1 2

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

6 answers: sort voted first

The bulk of RAM is used to cache data from disk. Unless you are implementing some NUMA limitations the cache is available across the processors.

How large are your databases? What else is running on the system?

As a quick set of numbers I would go with 2.5-3 GB for the O/S, 4 GB for the main instance and 1 for the Reporting instance. Baseline from there and adjust as necessary.
more ▼

answered Jun 21, 2011 at 11:26 AM

Blackhawk-17 gravatar image

Blackhawk-17
11.9k 28 31 36

Blackhawk,

Thanks for your quick response. Here are the system particulars.
6GB Database (will grow) and there will be more databases.

Reporting Services and SSIS are the only other 2 things running on the machine.

If I were to say, there would be 30 concurrent users of the reports at any given time..how would that metric affect the amount of needed memory?
Jun 21, 2011 at 11:49 AM dregalia
The problem comes down to a case of Your Mileage May Vary (YMMV). The reports may be lightweight so number of concurrent users may be a non-factor. The big thing is to ensure that the O/S has enough RAM to operate and that you strike a balance with SQL Server's usage of it.
Set it up - observe - adjust - repeat.
Jun 21, 2011 at 11:58 AM Blackhawk-17
The problem comes down to a case of Your Mileage May Vary (YMMV). The reports may be lightweight so number of concurrent users may be a non-factor. The big thing is to ensure that the O/S has enough RAM to operate and that you strike a balance with SQL Server's usage of it.
Set it up - observe - adjust - repeat.
Jun 21, 2011 at 11:58 AM Blackhawk-17
The problem comes down to a case of Your Mileage May Vary (YMMV). The reports may be lightweight so number of concurrent users may be a non-factor. The big thing is to ensure that the O/S has enough RAM to operate and that you strike a balance with SQL Server's usage of it.
Set it up - observe - adjust - repeat.
Jun 21, 2011 at 11:59 AM Blackhawk-17

So, If there was a formula put to it.. Say I had a report that was pretty beefy, and I had to budget for it, Could I say something like (.20 * 'dataset size' * #users) = memory budget for this report?

Would there be another metric that I could use for the Memory? Say, I had a 15GB Database, 5GB of which were indexes, with 10 users.. Would it even be possible to construct a formula for that to gauge min. required memory?
Jun 21, 2011 at 12:29 PM dregalia

There is no magic formula. It comes down to access patterns and data required to satisfy queries. Sometimes a small subset is all that is ever used and aged data is rarely touched. Other times almost all the data is required every time out. Only you can determine how much RAM your typical usage scenario may use.

But... RAM is probably the cheapest addition, easiest to configure, and the most bang for your buck so load it up as much as you can.

If you see excessive PageFile use then you need more :)
Jun 21, 2011 at 12:50 PM Blackhawk-17
(comments are locked)
10|1200 characters needed characters left
8gb might be a bit shy on memory, but the real question is, what kind of disks are you talking about. Yeah, get as much memory as you can because the more stuff you can put into cache and keep there the better, but your performance bottleneck will be, most of the time, I/O. I'd focus more of my efforts there than anywhere else.
more ▼

answered Jun 21, 2011 at 12:13 PM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
98.1k 19 21 74

Grant, The Hard drives on this are 4 146GB 10000 RPM Drives in Stripe 5. I've learned to never skimp on disk.. for 2 reasons.. 1, thats where the memory flows over to, and 2. It's all about the IO.. Thanks for your input.. It's never a bad idea to beef up on disks.

Jun 21, 2011 at 12:26 PM dregalia

RAID 5? Good for the business, not optimial for the disks, but pretty normal. It sounds basically good. Best disk is when you can get RAID 10, but most companies don't want to pay for that everywhere because it's frankly quite expensive.

Crud. Added that as an answer, not a comment. Rookie.
Jun 21, 2011 at 01:16 PM Grant Fritchey ♦♦
(comments are locked)
10|1200 characters needed characters left

You can check PerfMon counter, SQL Server Memory Manager -> Target Server Mermory, This indicates the amount of Memory that sql server willing to consume.

Also the counter, Process -> Working Set, This tells you much memory is SQL Server consuming, and if this value exceeds MAX SERVER MEMORY then you need to add more ram for SQL Server.
more ▼

answered Jun 21, 2011 at 08:42 PM

Cyborg gravatar image

Cyborg
10.6k 36 40 45

(comments are locked)
10|1200 characters needed characters left
It is really hard to believe that someone would go through the expense of purchases 4 quad core processors and skimp on memory at 8 GB. I just ordered 64 GB for a server and it cost just over 2k. I would ask to meet the person who put together the spec on that server to meet me in the parking lot after work.
more ▼

answered Jun 22, 2011 at 06:29 AM

BradleySQL gravatar image

BradleySQL
555 3 8 9

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

For me, I'd go for 1-1.5GB for the OS if there are no applications running from the server (So setting the SQL Instance to use max 6.5GB) and get the server running. Download a trial version of SQL Monitor from RedGate (http://www.red-gate.com/products/dba/sql-monitor/) and use the 14 days to make sure you have it somewhere close. I'd possibly expect to have to reduce the amount of RAM SQL uses in order to allow more for SSRS if the box is hosting that too. Also get a copy of Confio Ignite Free edition to keep a track on all (up to 25) of your servers.

Note: dont install SQL Monitor or Ignite on the server in question, use a different box so you dont make any problems worse.
more ▼

answered Jun 22, 2011 at 07:01 AM

Fatherjack gravatar image

Fatherjack ♦♦
42.3k 75 78 108

(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:

x713
x580
x245
x56

asked: Jun 21, 2011 at 11:15 AM

Seen: 11309 times

Last Updated: Jun 21, 2011 at 11:15 AM