The optimizer compares this estimated plan to actual execution plans that already exists in the plan cache . If an actual plan is found that matches the estimated one, then the optimizer will reuse the existing plan, since it’s already been used before by the query engine. This reuse avoids the overhead of creating actual execution plans for large and complex queries or even simple plans for small queries.
select *from employee where id = 1
When this statements begins execution it will compiled to execution plan and saved into cache. If any changes to the query like value of id or even if put an extra blank spaces between two words, again it will be compiled and new query plan is created.
|Changed id (Compiled and saved to cache)
||select *from employee where id = 2
|Add an extra blank spaces- (Compiled and saved to cache)
|| select *from employee where id = 2
Each execution of TestQueryPlan procedure create a new execution plan.
During each execution compilation occurs and more sql server cache memory is used for new plan.
How to reuse query execution plan? Using Stored procedure.
declare @statement nvarchar(max)
declare @var nvarchar(100)
set @statement = N’select *from employee where id = @id’
exec sp_executesql @statement, @var, 1
exec sp_executesql @statement, @var, 2
Also if the DB is known to have a lot of Ad-Hoc SQL queries, it is advisable to turn on” Forced Parameterization” which is available from SQL Server 2005 and above.
Basically in stored procedure query execution plan is reused. Here compiled once and for every new id plan is reuse.