Optimizing Queries in Deployed Applications by Using Plan Guides

SQL Server 2005 introduces the sp_create_plan_guide system stored procedure for creating plan guides to optimize the performance of queries. This procedure 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.

In This Section

Topic Description

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.

See Also

Other Resources

Query Performance

Help and Information

Getting SQL Server 2005 Assistance