How to reuse query execution plan?

How to reuse query execution plan?

Before a query, batch, stored procedure and dynamic sql statements begins execution in SQL server the batch gets compiled into a plan. Compiled plans stored into a part of sql server memory called plane cache. Then plan is executed to produce result. When next time that same batch again executed, server will search the plane for that query in the cache instead of compilation.

Steps for Creation Of Execution Plan  When a sql statement is submitted to sql server, it is first parsed by sql parser which verifies the syntax and converts the query into relational algebric expressions. Then the query optmizer constructs the execution plan based on several rules and cost of executing the query. Once the execution plan is generated, action switches to the storage engine where query is actually executed, according to the plan. There are two distinct types of execution plan. The plan that outputs from optimizer is known as estimated execution plan and the other plan represents the output from actual query execution.It is expensive for the Server to generate execution plans so SQL Server will keep and reuse plans wherever possible .As they are created, plans are stored in a section of memory called the plan cache.   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. Example :  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’            set @var = N’@id int’              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.