Share via

Plan Guides Are Your Best Friends

  • Article

Plan guides are used to optimize the performace of a query without modifying it. They are intended for advanced users and help in situations where a query submitted through an application can not be changed. For more details on this feature please visit https://msdn2.microsoft.com/en-us/library/ms190417(SQL.90).aspx.

In this blog, I will give some insight into its implementation. There are three important operations related to planguides.

1. Apply

 In sql server all statements in a batch or module (proc, trigger, etc.) are first compiled before being executed. After a batch/module is parsed, but before it is normalized/compiled (where binding, optimization, etc. occurs), we lookup metadata for any matching planguides. We iterate over all planguides for a given batch/module and find the set of statements in the batch/module to which they would apply. All these statements are modified to attach the query hint specified in the plan guide. At this point there is no difference between a query hint applied through a plan guide and a one that is directly applied. If a query hint already exist, it is replaced by the one specified in the plan guide. Binding and optimization proceeds without any knowledge about plan guides.

2. Create

 Plan guide DDL (create, drop, enable, etc.) are serialized by acquiring an exclusive lock on a database level resource. All compiles within a database acquires a shared lock on this resource to serialize with the plan guide DDL.
The batch/module specified in sp_create_plan_guide is parsed like a regular batch/module. Immediately after parsing, the statement and the query hint specified in sp_create_plan_guide are parsed in the same context. The statement is matched with one or more statements in the batch/module and the query hint is applied. The compilation continues like a regular batch/module. During compilation plan guides from the metadata will be applied (described above). If a duplicate is detected, an error will be raised and creation would fail.

 After the bath/module is successfully compiled, the plan guide is persisted in the metadata. Appropriate cached entries are flushed so that the subsequent batch/module exeution picks up the newly created planguide. Except for template, only the plans for affected batch/module are flushed. Note that this feature can be indirectly used to flush a single cache entry. (In future versions there may be special commands for flushing cache at a finer granularity when compared to dbcc freeproccache.) For templates, plans for all single query batches are flushed.

3. Control (drop, enable, disable)
 
 Plan guides for control operations can be identified individually or be scoped by a database. Affected plan guides are retrieved from the metadata and requested operations are performed. Cache flush policy is same as that for create, except for batches all plans with same hash value as that of the batch in the plan guide are flushed. As hash conflicts are rare on the batches, it is unlikely that plans for unaffected batches would be flushed.

Thanks

Comments