SQL Server Performance

Question

This is rather a generic question.

I've always understood that SQL Server likes RAM, the more the better.

If for example I had a SQL Server with just a single 2GB database and 8GB RAM, would there be any performance benefit to upgrading to 16GB RAM?

I guess what I'm getting at is that if SQL Server can fit the entire database into the RAM it currently has, would more RAM make any difference?

Thanks

• TomTom: Depends. Is your database large enough? If it already fits, more ram obviously will not really help.
• Crono: Actually it might help if your server does more than running an Sql Server database, serves a large group of users, different client applications, etc...
• Tim Schmelter: You can create sql queries which need more RAM than the entire database(cross joins).
• CiucaS: It depends on how many process you run with the SQL, what I mean is that if a Server has more aplication running the 8GB of RAM memory would be devided to all apps and maybe there won't be enough RAM for SQL Server. But other than that I don't think it will influence the performance, and anyhow I don't think SQL uses 2GB RAM for a 2GB Database.
• Paul White: Perhaps. 2GB might be enough to hold the data and index pages, but extra memory can be used for other purposes e.g. caching execution plans and sorting/hashing.

Answer 1

SQL Server does indeed like RAM and will take everything it is allowed to. For that reason, an upper limit of RAM should be assigned, leaving sufficient for the OS and anything else running on that server.

It would seem your 2GB database would easily fit into your existing RAM, but again, it depends on what else is running on your server. In addition to your buffer pool, you have the plan cache, and other applications - SSIS, 3rd party apps?

The only way to tell if an upgrade would be of benefit would be to monitor your memory usage via perfmon, DMV or any other appropriate means to see if your server is using fully the existing memory.

I found this great Technet article (http://technet.microsoft.com/en-us/library/ms176018.aspx) that explains which counters to monitor. Is also explains the conditions where adding more memory is appropriate: -

The Buffer Cache Hit Ratio counter is specific to an application. However, a rate of 90 percent or higher is desirable. Add more memory until the value is consistently greater than 90 percent. A value greater than 90 percent indicates that more than 90 percent of all requests for data were satisfied from the data cache.

If the Total Server Memory (KB) counter is consistently high compared to the amount of physical memory in the computer, it may indicate that more memory is required.

Answer 2

SQL is a programming language that use static data and make operation on them, like any other programming language.

When you make a query, SQL build some temporary table and allocated memory to variables. So if you do very complex operation or multiple user query at the same time, having more memory will help finish the query faster.

It's more how much your queries are complex rather than how much data you are storing.