Forum Discussion
execution plans and buffer pool
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
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