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
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
