SQL Server: sp_create_plan_guide system stored procedure

Plan guides can be used to optimize queries without modifying the statement directly. In a plan guide, DBA/Developer includes the query to be optimized, and either an OPTION clause with query hints, or a query plan that should be used during optimization. The sp_create_plan_guide system stored procedure accepts the following parameters:

@name - Specifies a unique plan guide name.

@stmt - Specifies the Transact-SQL statement or batch associated with the plan guide.
@type - Specifies the type of plan guide. Valid values are OBJECT, SQL, and TEMPLATE.
@module_or_batch - Specifies the module name if the plan guide applies to a specific module, such as a stored procedure.
@params - Specifies an optional list of parameters to be used for SQL and template plan guide types.
@hints -Specifies an OPTION clause that includes hints to be used during optimization.

After DBA/Developer creates a plan guide for a SQL statement, SQL Server matches the Transact-SQL statement to the plan guide when the query executes, and uses the specified OPTION clause hints or query plan for the statement. In this scenario, DBA/Developer wanted to force recompilation of the first SELECT statement in the stored procedure. DBA/Developer can accomplish this by creating a plan guide for the statement that includes the RECOMPILE query hint. This will force statement-level recompilation, and recompile the statement each time the stored procedure executes. Plan guides can be especially useful if DBA/Developer needs to add hints to the query to optimize the query without directly modifying it, such for deployed applications for which DBA/Developer cannot or do not want to modify existing code. DBA/Developer should not re-create the stored procedure including the WITH RECOMPILE clause. Including the WITH RECOMPILE clause would force recompilation for the entire stored procedure each time it executed. In this scenario, DBA/Developer only wanted to force recompilation for the initial SELECT statement. DBA/Developer should not set the PARAMETERIZATION option to FORCED. Forced parameterization can limit the frequency of query compilations and recompilations in a database. This option can only be applied to the entire database, not used to recompile a single query within a stored procedure. DBA/Developer should not recompile the stored procedure using the sp_recompile system stored procedure because this would recompile the entire stored procedure the next time it executed. In this scenario, DBA/Developer only needed to recompile the first query. In addition, DBA/Developer wanted recompilation for the first query to occur each time the stored procedure executed.