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 executed for the first time, the data gets placed into the buffer pool. Question is, when the same query runs for a second time, does the same execution plan get used to pull data from the buffer pool?
thanks!
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
- thanks LainRobertson. However, I don't my question has been answered. When the pages get cached into the buffer, is the data pulled in the same manner that the data gets pulled from disk? For example, if the execution plan shows that an index was used, is the same index used to retrieve the data from the buffer cache?
Hi, Rob.
As per the first part of the answer, there's no unambiguous yes or no to that. The Microsoft article speaks to that variability in much greater depth than my summarisation.
A reductionist answer to your question - assuming at least that subsequent calls of the same query are within the same session as the first and no impacting SET operations have been run in between the first and subsequent calls - is that subsequent re-queries will be fetched in the same manner as the first iteration using the same query execution plan.
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.
