Query Processing and Execution - SQL Server 2012 Internals and Troubleshooting - page 129

Plan Guides
Plan guides, which were added in SQL Server 2005, enable the DBA to affect the optimization of
a query without altering the query itself. Typically, plan guides are used by DBAs seeking to tune
query execution on third-party application databases, where the T-SQL code being executed is
proprietary and cannot be changed. Typical examples of applications for which plan guides are most
likely to be needed would be large ERP applications such as SAP, PeopleSoft, and so on.

Although plan guides were i rst added in SQL Server 2005, signii cant enhancements, primarily
regarding ease of use, were made to them in SQL Server 2008.

There are three different types of plan guide:

➤

Object plan guide — Can be applied to a stored procedure, trigger, or user-dei ned function

➤

SQL plan guide — Applied to a specii c SQL statement

➤

Template plan guide — Provides a way to override database settings for parameterization of
specii c SQL queries

To make use of plan guides, the i rst step is to create or capture a “good” plan; the second step
is to apply that plan to the object or T-SQL statement for which you want to change the Query
Optimizer's behavior.

QUERY PLANS

Now that you have seen how your T-SQL is optimized, the next step is to look at the query plan that
the Query Optimizer generated for it. There are several ways to view query plans, but perhaps the
easiest is to view the graphical plan using SQL Server Management Studio (SSMS). SSMS makes this
extra easy by providing a context-sensitive menu option that enables you to highlight any piece of
T-SQL in a query window and display the estimated execution plan, as shown in Figure 5-5.

FIGURE 5-5