I want to understand one basic thing in relation to databases specifically on SQL Server. SQL Server will perform any actions in-memory.
But lets say if we have a 100GB table on the Server with 2GB, if we do select * from table then how will it handle?
My assumption:
it pushes the data to in-memory, once the allocated memory gets full, then it serve that data to the client, when client receiving the data(lets say 100MB received) then the sent data will flush and read 100MB more data from the disk.
Best Answer
Not exactly, but in effect pretty close.
Pages are read as they are needed (imagine the query thread is reading page after page). If a page isn't in cache, then it is read into cache. The cost in cache for that page would then be 1, since it has been accessed 1 time. When there is memory pressure (imagine your thread continues and reaches a point where all memory is now used), then memory pressure will trigger removal of "cheap pages" from cache. A page which has been read only only once (like the pages read in the beginning of your query processing) will have a cost of 1, and are likely to be targets from removal compared to other pages, which might have been read multiple times (having a cost higher than 1).
It is basically like any cache handling mechanism, in principal.