Logical reads time 8KB more than memory on instance I am working on an instance of SQL Server and was using Redgate SQL Monitor. I saw a query in the top 10 list with 1 execution which had logical reads more than the memory for the instance by a factor of around 100 and the duration of the query was only a few minutes. How is that possible? I mean how can 8KB pages times the number of logical reads be more than the memory? What is it that I don't understand?
Thanks
If SQL Server reads 10 pages they don't all have to be in memory at the same time. It can also count reads of the same page multiple times.
To simplify, let's say SQL Server has limited the buffer pool to 8KB (conveniently, the size of a page). Now, you run a query that requires SQL Server to read 3 pages. It can't fit all the pages into memory at once, so:
Action                    Number of reads so far
------------------------  ----------------------
Loads page 1 into memory                       1
Discards/disfavors page 1                      1
Loads page 2 into memory                       2
Discards/disfavors page 2                      2
Loads page 3 into memory                       3

Now, let's say you use a CTE or self-join or something else, if SQL Server isn't able to re-use the same page while it is in memory, it will have to do that all over again. Even if it doesn't, if it has to access the same page twice while it is in memory, that's two logical reads. If an operator in a query plan (like a key lookup) has 10 executions, those are additional logical reads. If a foreign key has to be validated, there are some more. If a trigger runs as part of the transaction, worktables are involved, a sort spills to disk, etc. just keep piling on the reads. Just because all those reads can't fit into memory doesn't mean they don't still have to happen.
There is no direct correlation between read activity and max server memory / buffer pool size. You just need to remember that page reads aren't distinct and they don't all have to happen at the same instant in time, especially if you have a lot of churn in your buffer pool, in which case they have be spread out.