Databases Reference
In-Depth Information
Plan guides, a new feature introduced with SQL Server 2005, can help you in these
instances. Plan guides essentially work by keeping a list of queries on the server, along
with the hints you want to apply to them. To use a plan guide, you need to provide
SQL Server with the query that you want to optimize, and either a query hint using the
OPTION
clause, or an XML plan using the
USE
PLAN
hint, which will be explained in the
next section. When the query is optimized, SQL Server will apply the hint requested
in the plan guide definition. You can also specify NULL as a hint in your plan guide to
remove an existing hint in your application.
As well as allowing you to apply hints to code which you can't or don't want to change,
plan guides make it easier to apply, update, and remove query hints. Plan guides can also
match queries in different contexts; for example, a stored procedure, a user-defined scalar
function, or a stand-alone statement which is not part of any database object.
You can use the
sp_create_plan_guide
stored procedure to create a plan guide, and
the
sp_control_plan_guide
to drop, enable or disable plan guides. For more details
on how to use these stored procedures, you should investigate Books Online, which has
much more detail than we could cover here. You can see which plan guides are defined in
your database by looking at the
sys.plan_guides
catalog view.
To make sure that the query in the plan guide definition matches the query being
executed, especially for stand-alone statements, you can use the Profiler's
Plan
Guide
Successful
event class, which will show whether an execution plan was successfully
created using a plan guide. On the other hand, the
Plan
Guide
Unsuccessful
event will show if SQL Server was unable to create an execution plan using a plan
guide, meaning that the query was instead optimized without it. You can see the
Plan
Guide
Unsuccessful
event, for example, when trying to force a Merge or Hash Join
with a non-equality operator in the join condition, as shown in Listing 7-4, earlier in
this chapter.
Let's see an example of these events. Suppose we want to use plan guides to avoid a Merge
or Hash Join in our previous query, in order to avoid high memory usage. Before running
this code, open a SQL Server Profiler session, connect it to your instance of SQL Server,
