What does SQL server do with the query output once it is read by the client?
Assuming a SELECT query with multi-table join is executed, which resulted in selection of 100000 rows. The join operation will be performed in memory and the result will also be held in memory until the client (for example - ssms) consumes the entire result?
Once client consumes the result, does the SQL server purge the query result from memory?
The join operation output will be performed in memory and the result will be held in memory until the client (for example - ssms) consumes the entire result.
This is a complete fallacy. Depending on the join operation, if there are no blocking operators (such as
Hash) in the query plan then it may be the case that there is only ever one row in memory at any given time.
In other words, a completely non-blocking plan (eg joins only using
Hash) will feed each row read from the tables through a series of operators and out to the client. If the client blocks then the next row will simply not be read.
In the case of a blocking operator, the operator itself has memory (or disk space if necessary) to hold the resultset. As soon as the operator completes (which may be before the overall plan completes) the data is dropped. In this case it never remains beyond the lifetime of the query.
Likewise, operators that cache rows as they pass through, or prefetch rows, do not hold on to them beyond the point that the operator completes.
The only things that are cached beyond the lifetime of the query are the query plan itself (which tells the server how to service the query) and the raw pages from the tables (which are kept in a separate area called the buffer pool). The query result is not cached at all.