Forum Discussion
execution plans and buffer pool
hi, I'm using SQL Server 2022. I just had a basic question. We all know that the execution plan describes how data is pulled from data files including if indexes are used. However, after a query is ...
Hi, Rob.
The answer to your first implied question on "does the same execution plan get used?" is "it depends", but broadly-speaking, if the query is the same, the relevant "SET" options are the same (i.e. different connections optionally across different clients can yield different set options) and it's run in the original user context, it's likely the same execution play will be used.
A result set is never cached in SQL Server, however, the underlying data pages containing the result rows are. All this means is that the response time for subsequent queries may be lower since the underlying data is re-read from memory, with only any variations read from storage.
How effective this page caching is can be measured using the SQL Server: Buffer Node: Page life expectancy performance counter, where shorter times mean pages are not being cached for long.
Cheers,
Lain
> 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 client session settings 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 Stack Exchange's site for DBAs and ask your question (read the advice in Minimal, Complete, Verifiable Example 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
