How SQL Server Matches Plan Guides to Queries
SQL Server matches plan guides of type OBJECT to queries that appear inside the stored procedure, trigger, or function (module) named in the @module_or_batch argument of the sp_create_plan_guide stored procedure. If a plan guide exists on the module, the plan guide is matched to the specified query.
For queries submitted inside batches, SQL Server matches plan guides to them by first trying to match the query to a SQL-based plan guide, next by trying to parameterize the query, and then by trying again. The following illustration provides an overview of this process.
.gif "How SQL Server matches plan guides to queries")
The SQL Server query optimizer recognizes an executed batch as a request to compile each statement within the batch.
For a specific statement within the batch, SQL Server tries to match the statement to a SQL-based plan guide whose @module_or_batch argument matches that of the incoming batch text, including any constant literal values; and whose @stmt argument also matches the statement in the batch. If this kind of plan guide exists and the match succeeds, the statement text is modified to include the query hints specified in the plan guide. The statement is then compiled accordingly.
If a plan guide is not matched to the statement in step 2, SQL Server tries to parameterize the statement by using forced parameterization. In this step, parameterization can fail for any one of the following reasons:
- The statement is already parameterized or contains local variables.
- The PARAMETERIZATION SIMPLE database SET option is applied (the default setting), and there is no plan guide of type TEMPLATE that applies to the statement and specifies the PARAMETERIZATION FORCED query hint.
- A plan guide of type TEMPLATE exists that applies to the statement and specifies the PARAMETERIZATION SIMPLE query hint.
If forced parameterization succeeds, SQL Server tries to match the parameterized form of the statement to a plan guide of type SQL that was created on that parameterized statement. If this kind of plan guide exists and the match succeeds, the statement text is modified to include the query hints specified in the plan guide. The statement is then compiled accordingly. If no such plan guide is matched, the parameterized statement is compiled without first being modified by a plan guide.
If parameterization fails in step 3, SQL Server tries to parameterize the statement again. This time, SQL Server applies the default simple parameterization behavior. The resulting query is then compiled directly. Plan guides are not matched to the results of simple parameterization.
Note
The RECOMPILE query hint is ignored in plan guides of type SQL that apply to queries parameterized through forced or simple parameterization. A warning is also issued.
See Also
Concepts
Simple Parameterization
Forced Parameterization
Optimizing Queries in Deployed Applications by Using Plan Guides
Other Resources
Query Performance
sp_create_plan_guide (Transact-SQL)