SQL Server Performance 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
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.