There is a very clear and present limit to how much performance you can achieve in an SQL Server simply by adding RAM. If you don’t have the necessary indexes or statistics on a table, the query plan produced by the Query Optimizer may be such that no amount of RAM will solve the problem. I found this to be true through practical experience.
I had a database that had a filter table, specific to different configurations users could establish, that would be joined to detail data during execution time. The filter table had a column that joined to the same value the detail table contained. It had a second column that was user configurable, but never used. The second column had been in the database for years as a future implementation, but had not been used with anything other than the default value. Then one day, the business started using the new feature. Everything was fine for about a month. Then things started to get slow, and continued to slow down more each day.
The database server had 2x Ram as the entire database. Ram was not solving the problem completely. It was the only thing that kept it limping along. I got out my performance tuning tools, and even brought in some external hotshots to help identify what was going on. We could not find the cause of the performance decrease, and things were getting really rough.
Eventually, I was able to narrow into the query using this little filter table, that had grown from a few records to thousands of records. This was really nothing as many of the tables had millions or billions of records. However, the change of the data within that table was enough to alter the query plan when joining it to a million record table so that the query was horrible. I created a composite index on the two columns in my filter table, and the performance was reduced from many seconds to < 10 milliseconds.
The point is that if you don’t have the correct indexes and statistics, your database cannot always take advantage of a large volume of cache.
Cheers,
Ben