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
Best Answer
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.
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.
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.