4

I have been thinking about how the execution plans act in SQL SERVER after a restart of service. I know that in each query execution in the cache memory exist a process to parse, compile and optimize the requested query applying execution plans based on statistics and other parameters in SQL. All those execution plans are stored in buffer pool area in the storage engine. Actually, after each execution, those plans are updated to let the SQL SEVER learn itself about new improvements.

Here is my doubt: what happens with the execution plans in cache memory each time that I decided to restart my SQL service? All the improvements in the buffer pool area are stored and reloaded to be available in new executions? All execution plans are reloaded as last versions before the restart, or instead, SQL will need to create new execution plans library in order to keep on improving the executions?

Please, guys if you can explain that behavior I will really appreciate. thanks so much

Improve this question

1 Answer 1

Reset to default
5

This is what happens when you restart SQL Server. Correct term will SQL Server has to create new execution plans, choose the 'reasonably good one' and save it in the cache to reuse.

or instead, SQL will need to create new execution plans library in order to keep on improving the executions?

You have some misconception about execution plans in your question.

Plans are not updated after each execution. SQL Server keep tracks of how many time a plan was used, last used, and few other metrics. If you have memory pressure the usage metrics is used to decide which plans need to be removed to free up memory.

Actually, after each execution, those plans are updated to let the SQL learn itself about new improvements.

You can manually remove plans from cache, details here.

There are improvements announced for SQL vnext (2017) to adjust/modify query plan on the fly called Adaptive Query Processing . You can watch a video by Microsoft Product Manager Joe Sack.here and read here.

Improve this answer
5
  • Thanks so much for your answer, so just to be pretty sure the answer is yes. after a SQL restart the buffer pool is clean up and SQL needs to start to save new execution plans right.
    – Andres Angel
    May 9, 2017 at 12:54
  • Yes you are correct. Please accept the answer if it answered your question 100%.
    – SqlWorldWide
    May 9, 2017 at 13:04
  • @SqlWorldWide I dont understand the answer and have the same question. Can you please clarify what happens with existing query plans on restart? I am wondering e. g. if I add an index that makes a query faster but have a bad plan already, if that index would then be automatically taken - at least after a restart.
    – Andreas Reiff
    Nov 5, 2019 at 23:04
  • I just read that creating an index will make SQL Server recreate the plan on next execution, which is good and what I want. sqlworkbooks.com/2017/08/… Same seems to hold true for Index Rebuild, which probably is regularly executed on any index as part of maintenance. Still, knowing whether a restart causes the same would be interesting.
    – Andreas Reiff
    Nov 6, 2019 at 7:35
  • A restart will cause the engine to generate a new plan and it will take into consideration all index and statistics present upon restart.
    – SqlWorldWide
    Nov 9, 2019 at 2:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.