To manually remove a single plan or all plans from the cache, we use DBCC FREEPROCCACHE (Transact-SQL).
Note: To all those who are not aware of what execution plan is, please go through the below links (Article may be bit lengthy, but till day no one has documented the way he(Grant Fritchey) did)
https://www.simple-talk.com/sql/performance/execution-plan-basics/
https://www.simple-talk.com/sql/performance/graphical-execution-plans-for-simple-sql-queries/
How SQL Server does this automatically for us?
Execution plans remain in the procedure cache as long as there is enough memory to store them.
When memory pressure exists, the Database Engine uses a "COST-BASED" approach to determine which execution plans to remove from the procedure cache.
To make a cost-based decision, the Database Engine increases and decreases a current cost variable for each execution plan according to the following factors.
1) When a user process inserts an execution plan(new query) into the cache, the user process sets the current cost equal to the original query compile cost;
for ad-hoc execution plans(ex: CTE query), the user process sets the current cost to zero. Thereafter, each time a user process references an execution plan, it resets the current cost to the original compile cost;
for ad-hoc execution plans the user process increases the current cost. For all plans, the maximum value for the current cost is the original compile cost.
2) When memory pressure exists, the Database Engine responds by removing execution plans from the procedure cache.
To determine which plans to remove, the Database Engine repeatedly examines the state of each execution plan and removes plans when their current cost is zero.
An execution plan with zero current cost is not removed automatically when memory pressure exists; it is removed only when the Database Engine examines the plan and the current cost is zero.
When examining an execution plan, the Database Engine pushes the current cost towards zero by decreasing the current cost if a query is not currently using the plan.
3) The Database Engine repeatedly examines the execution plans until enough have been removed to satisfy memory requirements.
While memory pressure exists, an execution plan may have its cost increased and decreased more than once.
When memory pressure no longer exists, the Database Engine stops decreasing the current cost of unused execution plans and all execution plans remain in the procedure cache, even if their cost is zero.
4) The Database Engine uses the resource monitor and user threads to free memory from the procedure cache in response to memory pressure.
The resource monitor and user threads can examine plans run concurrently to decrease the current cost for each unused execution plan.
The resource monitor removes execution plans from the procedure cache when global memory pressure exists. It frees memory to enforce policies for system memory, process memory, resource pool memory, and maximum size for all caches.
The following examples illustrate which execution plans get removed from the procedure cache:
• An execution plan is frequently referenced so that its cost never goes to zero. The plan remains in the procedure cache and is not removed unless there is memory pressure and the current cost is zero.
• An ad-hoc execution plan is inserted and is not referenced again before memory pressure exists. Since ad-hoc plans are initialized with a current cost of zero, when the database engine examines the execution plan, it will see the zero current cost and remove the plan from the procedure cache. The ad-hoc execution plan remains in the procedure cache with a zero current cost when memory pressure does not exist.
Where can I see plan cache information?
dbcc memorystatus; -- (requires sysadmin privileges)
The most advanced and much awaited feature to persist execution plans(even when we restarts SQL instance) introduced in SQL Server 2016.
Will come up with the Query Store features on the next article.
