How much memory is needed to keep a table cached? (SQL Server 2008)

I run long and complex queries which mostly use one big table - 8GB, 40M rows. AFAIK, all/most rows are used in each query. I'm seeing lots of IO in the activity monitor - for the first query and for every subsequent one. The server is currently using 6.5GB of memory and I want to upgrade. Question is, how much memory is needed to avoid all these disk reads? Is it in the ballpark of the size of the table or more?

This is the SET STATISTICS IO output. BigTable is the one I'm asking about, SmallTable has a 1-to-many relation with BigTable. #entrance holds the output of the query (several hundred rows of output).

Table 'SmallTable'. Scan count 249005, logical reads 2829948, physical reads 2605, read-ahead reads 10395, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'BigTable'. Scan count 194004, logical reads 13482115, physical reads 33841, read-ahead reads 1181136, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#entrance__000000000023'. Scan count 0, logical reads 1568, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

This is a two part answer.

The first part is buy as much RAM as you can afford (as long as it'll fit into the server).

The second part is that you'll always see reads on data. The trick is to see if those reads are physical or logical. Physical reads are going down to the disk. Logical reads are read from the buffer pool in memory. You can see this by putting "SET STATISTICS IO ON" at the top of the SQL Script then running it in SSMS. On the messages tab you'll get specific IO information for each query that is being run, specifically you'll get the number of both logical and physical operations (reads in your case) that are happening on a table by table basis. If the physical read numbers are pretty high (these numbers are basically the number of blocks that were read so multiple by 8 then divide by 1024 to get a number in Megs) and then you need more RAM. If the logical read numbers are high, then you aren't actually going to disk but your query may need tuning, indexes created, etc.

(Note: For the purposes of this answer, I'm assuming SELECT statements only. INSERT/UPDATE/DELETE operations are a little more complex, but the same basics apply.)

StackExchange Database Administrators Q#5785, answer score: 8