In recent articles, I've discussed some of the ways to detect information about
the plans SQL Server is using to access data that your queries have requested.
Our next step is to explore some of the ways that you can affect what plan will
actually be used. Although Microsoft typically recommends that you let the SQL
Server query optimizer determine the plan to use for a query, sometimes you need
to provide a little guidance for the optimizer in the form of hints. Supplying
a query hint is a usually a straightforward change to your application code. However,
in certain environments, you might either have no control over the code itself,
or changing the code will break your licensing agreement or invalidate your support
guarantees.
In such situations, SQL Server 2005's plan guides feature can be helpful. Using a plan
guide, you can instruct SQL Server to use a particular hint every time it encounters a
specified query, and you don't need to change the query itself. Although plan guides are
frequently used with the new optimization hints included in SQL Server 2005, you can
use them with almost any hint. Let's start our exploration of
plan guides with an overview of how they work, then look
at why a plan guide might not work as intended.
Plan Guides Overview
SQL Server 2005 supports three types of plan guides, all of which can be created
by using the same procedure. Although almost all other objects in SQL Server
2005 use standard Data Definition Language (DDL)—CREATE to create the
object, ALTER to change the object properties, and DROP to remove the object—plan
guides haven't quite caught up with that paradigm. SQL Server 2005 provides
two new T-SQL stored procedures for working with plan guides: sp_create_plan_guide,
which creates a plan guide, and sp_control_plan_guide, which changes a plan
guide's properties or removes a plan guide. Web
Listing 1 (http://www.sqlmag.com, InstantDoc ID 96134) shows the general
form of the sp_create_plan_guide procedure.
The three types of plan guides are
- SQL—tells the optimizer to look for a specific SQL statement in
your application as specified in the @stmt parameter. If the @module_or_batch
parameter is NULL, the SQL statement must appear in a batch by itself. Otherwise,
the @module_or_batch parameter should include the exact text of the
entire batch that the statement will appear in.
- OBJECT—tells the optimizer to look for a specific statement in a
specific module. The module can be a stored procedure or function, and its
name is given in the @module_or_batch parameter.
- TEMPLATE—tells the optimizer to build a template based on a class
of queries. Since this type is a bit more complex than the other two, I'll
wait to discuss it further in a future article, after I've explained the other
two types. And because the @params parameter to sp_create_plan_guide is also
only used in conjunction with TEMPLATE plan guides, I'll discuss that parameter
in an upcoming article as well.
Creating an SQL Plan Guide
On my SQL Server system, the execution plan for the following query (which you
should run in the AdventureWorks database) shows that SQL Server will run this
query in parallel, over multiple CPUs:
SELECT TOP 10 *
FROM Sales.SalesOrderDetail
ORDER BY UnitPrice DESC;
Figure 1 shows the right side of the graphical
plan for the query.
Whether the plan you get for this query will involve parallelism depends on
a number of factors, not least of which is whether or not your server has multiple
processors available. If I'm having problems with parallel queries, I might
decide to force this query to be run on a single CPU, whenever the query is
run as a batch in an application. I could do so by creating a plan guide specifying
the previous query as the SQL statement and not specifying a value for the @module_or_batch
parameter. As the plan guide in Listing 1
shows, I've used the MAXDOP query hint as part of the @hints parameter to specify
a maximum degree of parallelism of 1 (i.e., a single CPU).
Once this plan is created in the AdventureWorks database, whenever the optimizer encounters the specified statement
in a batch by itself, it will create a plan that
uses only a single CPU. If the specified
query occurs as part of a larger batch, the
optimizer won't invoke the plan guide.
Enabling, Disabling, or Removing a Plan Guide
You can enable or disable a plan guide by using the sp_control_plan_guide procedure.
For example, I could disable the plan guide created in Listing
1 by using this statement:
EXEC sp_control_plan_guide
N'DISABLE',
N'plan_SalesOrderDetail_DOP1'
(Note that some statements in this article wrap to multiple lines because of
space constraints.) My query would then revert to potentially using multiple
CPUs when it was executed. I could re-enable the plan guide, also by using the
sp_control_plan_ guide procedure, like this:
EXEC sp_control_plan_guide
N'ENABLE',
N'plan_SalesOrderDetail_DOP1';
I'd also use sp_control_plan_guide to remove the plan guide, like this:
EXEC sp_control_plan_guide
N'DROP',
N'plan_
SalesOrderDetail_ DOP1';
Plan guides aren't intended to save on optimization time, only to make sure
that your applications can perform well if you've specified hints that can help
the queries they indicate. The queries themselves might run faster with the
hints forced by the plan guides, but optimization can take considerably longer.
Prev. page
[1]
2
next page 
