Optimizing Queries in Deployed Applications by Using Plan Guides

The sp_create_plan_guide and sp_create_plan_guide_from_handle system stored procedures can be used for creating plan guides to optimize the performance of queries. These procedures can be used when you cannot or do not want to directly change the text of the query. Plan guides can be useful when a small subset of queries in a database application provided by a third-party vendor are not performing as expected.

Understanding Plan Guides

Describes plan guides, the types of plan guides, and their uses.

Designing and Implementing Plan Guides

Explains what to consider before creating a plan guide, how to create a plan guide, and the limitations of plan guides.

Specifying Query Parameterization Behavior by Using Plan Guides

Explains how to create a TEMPLATE plan guide to specify whether a class of queries is parameterized when compiled.

Designing Plan Guides for Parameterized Queries

Explains how to create a plan guide on the parameterized form of a query.

How SQL Server Matches Plan Guides to Queries

Explains the logical architecture of matching queries to plan guides.

Using SQL Server Profiler to Create and Test Plan Guides

Explains how to facilitate query-to-plan guide matching by using SQL Server Profiler to capture the exact query text when you are creating a plan guide. Also explains how to use SQL Server Profiler to verify the match.

Using the INDEX and FORCESEEK Query Hints in Plan Guides

Explains how to use the INDEX and FORCSEEK query hints in a plan guide to control the indexes and index operations used by the query optimizer for the matching query.