Databases Reference
In-Depth Information
Once you've created your plan guide, you can enable or disable it at any time. For
example, the following statement will disable the previous plan guide, and the stored
procedure will again use a Hash Join when executed.
EXEC
sp_control_plan_guide
N'DISABLE'
,
N'plan_guide_test'
;
Listing 7-34.
To enable the plan guide again, use:
EXEC
sp_control_plan_guide
N'ENABLE'
,
N'plan_guide_test'
;
Listing 7-35.
Finally, to clean up, drop both the plan guide and the stored procedure. Note that
you need to drop the plan guide first, as you cannot drop a stored procedure that it is
currently referenced by a plan guide.
EXEC
sp_control_plan_guide
N'DROP'
,
N'plan_guide_test'
;
DROP
PROCEDURE
test
Listing 7-36.
USE PLAN
Finally, let's take a look at the
USE
PLAN
query hint, which was introduced with SQL
Server 2005. This takes the use of hints to the extreme by allowing the user to specify an
entire execution plan as a target to be used to optimize a query. The
USE
PLAN
hint is
useful when you know that a better plan than the Query Optimizer's suggestion exists.
