Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I have a quite a few servers deployed around the world. They are running Windows 2003 x64 with SQL Server 2005 x64 with 6 GB of RAM. The boxes do not have the best (or even an acceptable) configuration, because the guy that ordered them years ago didn't really know what he was doing.

The boxes are fairly consistently running out of memory, end up using the paging file and everything slows down. Typically the commit charge is 5.8GB and then when someone needs to do something intensive (e.g. run a report), that number goes through the roof.

I've been trying to get the powers that be order more memory, but I am getting massive opposition (e.g. make the software more performant, costs too much for all these servers, or prove that the box does not have enough memory, etc...).

Are there guidelines (or a formula) for how much RAM a box needs that I can present to non-techies, so that we can finally order more memory?

share|improve this question
    
Is the system developed in-house? –  Oskar Duveborn Feb 18 '11 at 18:13
    
@Oskar. Yes, I am the developer and the code is optimized to hell and back. There is simply a ton of data. –  AngryHacker Mar 3 '11 at 18:16
    
Then see my answer. This is the sort of thing I specialize in. –  mrdenny Aug 26 '11 at 17:28
add comment

7 Answers

up vote 6 down vote accepted

Not really any way to easily tell because it is entirely dependent on your usage and the application. You're maxing out a database server...how big is the database? What are your transaction stats?

The real-world limitations are obvious in your scenario. You're running for awhile on 6 gig without problem, then it's swapping and thrashing.Thus 6 gig isn't enough.

If performance is enough that it impacts business, then your higher ups should be hearing enough complaints that it is prudent to up the memory. Figure out what your time costs and then figure out how much it will cost to "tune" the server and troubleshoot the tuning, when memory added to the server may very well solve the issue for the cost of memory and less than a half hour of downtime.

You'll not know the exact amount of memory you need until you actually deploy in your real-life usage and work from there.

That said, you might want to verify that your application is truly the bottleneck. Run the windows performance monitor to see your disk i/o statistics and network throughput. See what your fragmentation level is as well (Google is a good friend here). You could try auditing the code for obvious issues too where a query is being massively inefficient (Google again).

But again it all depends on how badly this is impacting the business. Is it worth more to invest in the tuning, or is it bad enough to throw hardware at it first and then try tuning it?

share|improve this answer
    
+1 size and stats needed –  Oskar Duveborn Feb 18 '11 at 18:18
add comment

Hmmmm. Well, 6 gigs is a decent amount of ram, even for a big MSSQL install. You might actually want to look and make sure that your code really IS efficient. A 6 gig transaction is a bit unusual...I've worked on state-wide payroll systems that didn't top a gig on year end 1099 processing...And to have one running often? I don't know. What kind of data are you working with?

That being said, you can stuff as much RAM as you like in a 64 bit box, and ram is dirt cheap, so might as well put as much in there as you possibly can...Can't really have too much RAM on a database server.

share|improve this answer
    
you speak my mind. also make sure there're no huge app running other than os and sql server. –  DennyHalim.com Oct 24 '09 at 5:46
1  
Can't really have too much RAM on a database server. Perhaps not, but you can have RAM you wasted money on because it isn't being used. While I agree with the general idea that it pays to be generous on boxes doing certain kinds of tasks, I don't think that extends to just throwing resources into a system without understanding its requirements. –  RobM Feb 18 '11 at 18:20
2  
@robert: It's not like I'm advocating buying a blade server. Maxing the RAM in a server is pretty easy, and if you're running out of memory, then why not add more? I think the problem likely is in his code, but if you can fix the problem with a couple of hundred dollars worth of RAM, it's an efficient use of money. –  Satanicpuppy Feb 18 '11 at 18:23
1  
@robert: I agree. But I've too often seen people spend thousands on coders and consultants to fix a software problem, when throwing a bit more hardware at it will do the same thing for a fraction of the cost. –  Satanicpuppy Feb 18 '11 at 18:42
1  
@mdmarra: Eh. In 2012, sure. In 2009? Not so much. –  Satanicpuppy Jun 21 '12 at 21:14
show 5 more comments

as "Satanicpuppy" said, there is no such thing as too much RAM, but 6GB should be ok, maybe you should re-think on what your server does, I don't think that you have a "hardware" problem, you should focus on your SQL programming...

share|improve this answer
add comment

Before you jump the gun on buying more memory (or any other component) I would recommend running a performance analysis on the server. You can do this on your own using perfmon or you can look at using third party tools. You should analyze performance of both the OS and SQL server. IMHO, too often are we ready to throw hardware at a problem before a proper analysis has been done. For all you know at this point it could be a problem with a query, stored procedure, execution plan, disk I/O, CPU utilization, etc., etc. Memory pressure can often be a symptom of another bottleneck in the system.

share|improve this answer
add comment

When it comes to database servers there's no such thing as "enough" memory. Sure, it depends on what they actually do and run but if it's a constantly utilized database containing a lot of data and doing complicated queries - 6 GB could easily be grossly inadequate.

I'd start by upgrading one troublesome server to at least 32 or 64 GB and see if it helps. If not, turn to database tuning, application troubleshooting and debugging - which all, unless an idiot designed the database, cost a lot more than a few sticks of server-grade memory (and even if an idiot designed the thing, getting even obvious design errors fixed with retained support could prove quite a challenge).

That said, as someone else stated - it could be something else holding it back (apart from software design issues), like a lack of disk or network I/O performance - hiring a DBA pro to just go through basic SQL performance monitoring for a day could prove useful.

share|improve this answer
add comment

You should look at building more indexes. I think that in general, most people under-index their database.

This is still air-code, I haven't fully tested yet, but it should get you in the right direction

http://accessadp.com/2011/08/22/missing-indexes-great-script-for-determining-roi/

Select ‘create index IX_’ +
 sys.objects.name +
 isnull(replace(‘_’ + equality_columns, ‘,’, ‘_’), ”) +
 isnull(replace(‘_’ + inequality_columns, ‘,’, ‘_’), ”) + ‘ on ‘ +
 sys.objects.name +
 ‘(‘ +
 coalesce(equality_columns + ‘,’ + inequality_columns, equality_columns , inequality_columns ) +
 ‘) ‘ +
 isnull(‘ include (‘ + included_columns + ‘)’, ”)
 as CreateIndexSql,
 (CONVERT(Numeric(19,6), sys.dm_db_missing_index_group_stats.user_seeks)+CONVERT(Numeric(19,6), sys.dm_db_missing_index_group_stats.unique_compiles))*CONVERT(Numeric(19,6), sys.dm_db_missing_index_group_stats.avg_total_user_cost)*CONVERT(Numeric(19,6), sys.dm_db_missing_index_group_stats.avg_user_impact/100.0) AS Score,
 sys.schemas.schema_id,
 sys.schemas.name AS schema_name,
 sys.objects.object_id,
 sys.objects.name AS object_name,
 sys.objects.type,
 partitions.Rows, partitions.SizeMB,
 sys.dm_db_missing_index_details.equality_columns,
 sys.dm_db_missing_index_details.inequality_columns,
 sys.dm_db_missing_index_details.included_columns,
 sys.dm_db_missing_index_group_stats.unique_compiles,
 sys.dm_db_missing_index_group_stats.user_seeks, sys.dm_db_missing_index_group_stats.user_scans,
 sys.dm_db_missing_index_group_stats.avg_total_user_cost, sys.dm_db_missing_index_group_stats.avg_user_impact,
 sys.dm_db_missing_index_group_stats.last_user_seek, sys.dm_db_missing_index_group_stats.last_user_scan,
 sys.dm_db_missing_index_group_stats.system_seeks, sys.dm_db_missing_index_group_stats.system_scans,
 sys.dm_db_missing_index_group_stats.avg_total_system_cost, sys.dm_db_missing_index_group_stats.avg_system_impact,
 sys.dm_db_missing_index_group_stats.last_system_seek, sys.dm_db_missing_index_group_stats.last_system_scan
 FROM
 sys.objects
 JOIN (
 SELECT
 object_id, SUM(CASE WHEN index_id BETWEEN 0 AND 1 THEN row_count ELSE 0 END) AS Rows,
 CONVERT(numeric(19,3), CONVERT(numeric(19,3), SUM(in_row_reserved_page_count+lob_reserved_page_count+row_overflow_reserved_page_count))/CONVERT(numeric(19,3), 128)) AS SizeMB
 FROM sys.dm_db_partition_stats
 WHERE sys.dm_db_partition_stats.index_id BETWEEN 0 AND 1 –0=Heap; 1=Clustered; only 1 per table
 GROUP BY object_id
 ) AS partitions ON sys.objects.object_id=partitions.object_id
 JOIN sys.schemas ON sys.objects.schema_id=sys.schemas.schema_id
 JOIN sys.dm_db_missing_index_details ON sys.objects.object_id=sys.dm_db_missing_index_details.object_id
 JOIN sys.dm_db_missing_index_groups ON sys.dm_db_missing_index_details.index_handle=sys.dm_db_missing_index_groups.index_handle
 JOIN sys.dm_db_missing_index_group_stats ON sys.dm_db_missing_index_groups.index_group_handle=sys.dm_db_missing_index_group_stats.group_handle
 WHERE
 sys.dm_db_missing_index_details.database_id=DB_ID()
 AND (CONVERT(Numeric(19,6), sys.dm_db_missing_index_group_stats.user_seeks)+CONVERT(Numeric(19,6), sys.dm_db_missing_index_group_stats.unique_compiles))*CONVERT(Numeric(19,6), sys.dm_db_missing_index_group_stats.avg_total_user_cost)*CONVERT(Numeric(19,6), sys.dm_db_missing_index_group_stats.avg_user_impact/100.0) > 100
share|improve this answer
add comment

An easy way to see if you need more RAM is to chart the Page Life Expectancy perfmon counter. This counter tells you how long SQL Server thinks that data will be kept in the buffer pool before it needs to make room for other data. You want this number as high as possible. With 6 Gigs of RAM installed (you should have SQL set to max out at probably 4 gigs) you'll probably only be keeping data in memory for a few minutes at most, when someone runs a large report you'll see this number tank down to a few seconds. The more RAM you have, the longer data can be kept in memory, and the less reading from the disks will need to be done.

For example, the systems I'm working with at the moment have 256 Gigs of RAM and we keep data in memory for about 12000 seconds or so.

Please don't ask for a target number to hit, you just want the number as high as possible. Without knowing a LOT more about your systems I can't give a good number to shoot for.

share|improve this answer
add comment

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.