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.
Best Answer
what is the nature of your queries? SELECTs only? Or a mixture of DUI (DELETE, UPDATE, INSERTs)? and what is your recovery model? are you using the default transaction isolation level?
If you want to put all those pages in memory, then you will need to get more memory (but you knew that already). You want to know 'how much', but the truth is that depending on the nature of your queries you could still see some I/O issues even with enough memory to hold the entire table.
Consider ing all of the above...yeah, ballpark the size of the table on disk as the amount of memory. Just don't be surprised if you find you still have disk I/O afterwards.