Forum Discussion
execution plans and buffer pool
> We all know that the execution plan describes how data is pulled from data files including if indexes are used.
Execution plans do not describe how data is pulled from data files. Execution plans explain how your query is executed by SQL Server to return the desired results. Data is read from disk directly into the buffer pool memory, where it stays until that memory space is needed for other data. For a given table (or index), the data in the buffer pool is a copy of the data from disk - i.e. it's in the same format in memory as it is on-disk.
When you run a query, the engine creates a plan for converting the data contained in the tables and indexes into the results you asked for.
For example, if you're asking for a count of the rows in a table, SQL Server reads table/index rows from the buffer pool, counting the number of rows, returning the row count to the client (if pages from the table/index aren't in memory, they will be read from disk first, but that is a transparent process as far as query processing is concerned). The results (i.e. the count of rows) are never cached - only the table/index data is cached for future use.
The execution *plan* will be cached (under many circumstances) for use in future queries which precisely match both the format of the query text, including spaces, carriage-returns, and line feeds, and the semantics of the query the plan was generated for. The "semantics" I'm talking about here include things like the parameters used in WHERE clauses, the https://learn.microsoft.com/en-us/sql/t-sql/statements/set-statements-transact-sql that are in effect when the plan is generated, SQL Server settings for the maximum memory available to the query optimizer, the maximum degree of parallelism, etc, etc. If any of the semantics of a given query are different from those used in the cached plan, then a new plan will be generated to ensure the results are consistent with the declarative T-SQL statement used in the query. Note, the new plan will also be cached, for possible future use.
If you are asking about a specific query, and how the execution plan will be used, cached, and possibly re-used, you should head over to https://dba.stackexchange.com/ and ask your question (read the advice in https://dba.stackexchange.com/help/mcve first)
hi HannahVernon, let's say a query is executed where an index seek is used with a key or rowid lookup and the data is pulled from disk (not buffer cache). A minute later, the same query is executed and assume that the data is pulled from buffer cache this time. Question is, does the index now have the new (buffered) location of the key or rowid during the index seek operation? Just trying to understand how indexes get used when the data is retrieved from buffer, not disk.
Hi, Rob.
You've made a few assumptions, so let's make a few more about that second call made a minute later:
- The query is issued from the same SQL client session;
- No relevant SET options (I've already discussed this above) have been made since the first call;
- The syntax is identical (as might be expected from a static query in the same client application);
- The page life expectancy across all NUMA nodes is greater than 60 seconds.
The query plan has not changed and is therefore reused.
This has precisely nothing to do with the buffer manager. The query optimiser is abstracted from knowing anything about whether it's being fetched from disk or buffered memory.
The second query executes precisely the same way as the first time around. There's no "shortcuts" going on at that layer as the optimiser has no understanding of the concept of caching, which happens at a lower, distinctly separate layer.
Indexes are not "used" differently by the query optimiser depending on whether the data is retrieved from disk or memory.
Because the page life expectancy is greater than one minute, when all the relevant I/O commands are issued, the buffer pool manager simply fetches the data pages from memory (i.e. the buffer pool) and provides them back transparently - exactly as if they had been read from disk (as per the first query), meaning no physical storage reads took place.
If your page life expectancy per node is less than 60 seconds then there's a (good - depends on how many NUMA nodes your host has) chance that the second query will result in physical disk reads since data is not being cached briefly before replaced again with new data inside your scenario's one minute window between the first and second calls - none of which has anything at all to do with your execution plans. It just means the second query iteration likely won't be any faster than your first.
Cheers,
Lain
