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'm looking for opinions and resources as to how much performance will increase on a server with adding more RAM. What factors come into play? Is there a general calculation that can be performed?

share|improve this question
add comment

4 Answers 4

up vote 10 down vote accepted

That is entirely subjective...

  • Are you using SQL Server 2005 as a 64 bit program?
  • Is the database the only application on the server?
  • Did you profile the server to see if there's a crunch in memory? or disk I/O? Or network? Processor?

If you're seeing a crunch in performance, you would need to identify where the bottleneck is and go from there. Using the Windows Performance Monitor may help. Randomly throwing hardware at the problem may not help at all (although generally memory doesn't hurt).

share|improve this answer
    
adding to those questions, what kind of hard drives are being used? –  Keith Sep 15 '09 at 14:53
    
Thanks for the filtering questions. We're running 32-bit with PAE and AWE can be enabled for the SQL Server process if we add more RAM. Currently there's 4GB RAM installed, and the system seems to cap it at around 2.7GB. Yes, the database is the only app on the server. Processor use seems low enough. What are relevant counters for measuring disk I/O and network usage? Thanks again! –  paulwhit Sep 15 '09 at 18:45
    
(The SQL Server process is what won't grow higher than 2.7GB) –  paulwhit Sep 15 '09 at 18:46
    
This seems a little old but might help. sql-server-performance.com/articles/audit/… If you're maxing out the RAM available to the process you might need to look at going to a 64 bit OS and 64 bit version of SQL server, but others might have better insight on that. I was under the school of thought that if you're having to do little tricks to squeeze memory available to the process, you need to start looking at expanding memory and hardware available (bitwise, according to your installed mem and installed bit-types). –  Bart Silverstrim Sep 15 '09 at 20:51
add comment

In general you get best performane out of the database if the working set of data fits entirely into RAM. The working set is the subset of your data that is regularily read by queries (update and inserts always hit the database, so more RAM won't necessarily speed those up). So first you need to identify how much memory you really need to cache all relevant data. Then you need to look at the best way to provide that RAM to SQL server.

share|improve this answer
add comment

As far as I know, there are no calculations that will predict the increase in performance based on a given increase in the amount of memory. The relationship between the two is not linear, and there are always other factors involved, as noted by others. Assuming that you do not have a massive bottleneck elsewhere, though, adding RAM does almost always help.

One other thing to note: if you are running on 32-bit versions of Windows and/or SQL Server, you are restricted on the amount of RAM you can have to between 2GB and 4GB, depending on the configuration of both servers. If you upgrade both to the 64-bit versions, you can utilize more than 4GB of RAM to improve performance.

share|improve this answer
    
We're on a 32-bit server (Windows 2003 Enterprise Edition) with PAE enabled, and SQL Server will have AWE enabled after adding more RAM, so my impression is that it can use more than 4GB of RAM. Is this incorrect? Ref: sql-server-performance.com/articles/per/… –  paulwhit Sep 15 '09 at 18:41
1  
that is correct, PAE is fully supported on server editions of Windows (not so much anywhere else) and minus a tiny performance decrease due to the overhead, you have access to as much RAM as is installed. –  Jeff Atwood Sep 16 '09 at 1:07
    
hi Jeff! So the SQL Server process can also grow over 4GB itself on 32-bit Windows? AWE that lets that happen, right? –  paulwhit Sep 16 '09 at 22:45
    
Yes and yes. Without AWE, the SQL process can't access more than 2GB of memory. –  GilaMonster Sep 19 '09 at 10:52
    
Thanks for the confirmation GilaMonster –  paulwhit Sep 22 '09 at 14:36
add comment

As Bart suggested, you need to isolate and identify the top bottlenecks on the server and resolve them for optimal performance. As you are already on SQL Server 2005, you can use the wait stats on the server. Here is a query from Glenn's diagnostics queries. Here is the link. http://glennberrysqlperformance.spaces.live.com/blog/cns!45041418ECCAA960!1340.entry

-- Isolate top waits for server instance since last restart or statistics clear WITH Waits AS (SELECT wait_type, wait_time_ms / 1000. AS wait_time_s, 100. * wait_time_ms / SUM(wait_time_ms) OVER() AS pct, ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS rn FROM sys.dm_os_wait_stats WHERE wait_type NOT IN( 'SLEEP_TASK', 'BROKER_TASK_STOP', 'SQLTRACE_BUFFER_FLUSH', 'CLR_AUTO_EVENT', 'CLR_MANUAL_EVENT', 'LAZYWRITER_SLEEP')) -- filter out additional irrelevant waits SELECT W1.wait_type, CAST(W1.wait_time_s AS DECIMAL(12, 2)) AS wait_time_s, CAST(W1.pct AS DECIMAL(12, 2)) AS pct, CAST(SUM(W2.pct) AS DECIMAL(12, 2)) AS running_pct FROM Waits AS W1 INNER JOIN Waits AS W2 ON W2.rn

That being said, in general SQL Server is mostly constrained by memory, IO and CPU and in mostly in that order. You need to look at page life expectancy value using perfmon/sql server DMV's. SQL Server thrives if it has more memory. But note that I am against throwing hardware at a problem when it should be best to handle in TSQL areas. Most likely you need to look at queries thats doing most IO (these will likely thrash the buffer pool), create meaningful indexes, drop the unused indexes, work on the missing indexes, or optimizing the sql. Optimizing sql is where people fail usually because of lack of skills.

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.