SQL Server reuses any existing plan it finds, saving the overhead of recompiling the SQL statement.If no existing execution plan exists, SQL Server generates a new execution plan for the query.
The algorithms to match new SQL statements to existing, unused execution plans in the cache require that all object references be fully qualified.
For example, the first of these 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.
When a user process inserts an execution plan into the cache, the user process sets the current cost equal to the original query compile cost; for ad-hoc execution plans, 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.SQL Server has a pool of memory that is used to store both execution plans and data buffers.The percentage of the pool allocated to either execution plans or data buffers fluctuates dynamically, depending on the state of the system.The part of the memory pool that is used to store execution plans is referred to as the procedure cache.SQL Server execution plans have the following main components: When any SQL statement is executed in SQL Server, the relational engine first looks through the procedure cache to verify that an existing execution plan for the same SQL statement exists.