How much will SQL Server 2005 performance increase with more RAM?

Question

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?

Answer 1

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).

Answer 2

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.

Answer 3

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.

Answer 4

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.