7

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?

0

3 Answers 3

16

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 Sort or 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 Loop Join or Merge not 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.

2
8

Once client consumes the result, does the SQL server purge the query result from memory?

The raw pages of data (but not the calculated results) are cached in Memory, known as the Buffer Pool, and will remain cached (even after serving the original query) to be re-used as needed.

Things that can cause pages to be removed from the cache include needing room for new pages of data for a more recent query, Memory pressure, or server restarts. (There are probably other reasons as well that I'm not currently thinking of.)

2
  • Is the result of the join operation (or maybe a simple query) held in memory while it is being consumed by the client?
    – variable
    May 4 at 5:59
  • @variable As mentioned by others, the results are streamed to the client immediately, so typically are not held in Memory. Now there are certain operators that create temporary tables in Memory which can get re-used by different parts of the query plan (e.g. when a Table Spool or Index Spool occurs). So sometimes there is data stored in Memory that isn't the raw data pages, but also isn't the final results, and isn't accessible outside of the query plan that is currently executing, and goes away once finished being used by that executing query.
    – J.D.
    May 4 at 11:30
3

SQL Server caches plans and data, but it does not cache query results. If you have a scenario in which same query is run again and again, maybe the architecture can be improved. For example, query notifications can be used to notify the clients that data has changed - no need to keep asking if it has.

Oracle has got a result cache in addition to query notifications.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.