Buffer cache and plan cache

Question

If I m execute a query for first time which is taking 10 seconds and its fetching 100 GB of data , SQL Server need to fetch from disk the pages and put into buffer cache before come out with results. My question is what will happen, if I m cancelling the query while its executing query within 5 seconds, so I believe few pages might be already in buffer cache ,do SQL Server will flush out the pages and plan which its already in the buffer cache and plan cache or it will remain there in cache although I have cancel the query

Answer 1

do SQL Server will flush out the pages and plan which its already in the buffer cache and plan cache or it will remain there in cache although I have cancel the query

Pages will remain in the buffer cache even if the query is cancelled. These will be reused when the query is rerun (assuming they are still in cache) or by other queries needing the same pages.

Answer 2

Hi Mario2286-5314,

Agree with DanGuzman.
It will remain in the cache. And old and less used data will be cleared when the buffer cache fills up to make room for newer data. Please check Insight into the SQL Server buffer cache which might be helpful.

Best Regards,
Amelia

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.