Keeping Track of Your Plan Guides
The metadata view that contains information about plan guides in a particular
database is sys.plan_guides. This view contains all the information supplied
in the sp_create_plan_guide procedure, plus additional information such as the
creation date and last-modification date of each plan guide. Using the information
in this view, you can manually reconstruct the plan guide definition, if necessary.
However, there's no built-in mechanism to automatically script the plan guide
definitions, as you can do with most other SQL Server objects, so it's strongly
recommended that you save your actual sp_create_plan_guide scripts in case you
ever need to move your definitions to a new server.
What's Your Plan?
Keep in mind that plan guides aren't intended to speed up query-compilation
time. Not only does SQL Server first have to determine whether a plan guide
exists that could be a potential match for the query being compiled, but the
plan enforced by the plan guide must be one that the optimizer would have come
up with on its own. To determine that the forced plan is valid, the optimizer
has to go through most of the optimization process. Plan guides are intended
to reduce execution time for those queries in which the SQL Server optimizer
doesn't produce the best plan on its own.
The benefit of plan guides is twofold. First, if you suspect that a specific
hint might make a query run more efficiently, you don't need to change the actual
application code. You can create a plan guide to tell SQL Server what hints
to use, then test the query with the plan guide in place. If the plan guide
improves performance, you might at that time choose to add the hints to the
actual code. However, keeping the hints as part of plan guides means that at
any time in the future, if you decide the hints you've used are no longer optimal,
you can remove the hints merely by dropping or disabling the plan guides. Second,
plan guides can be a wonderful solution to query performance problems in situations
when you can't modify the source code to add hints on your own. This can happen
if you're using a third-party application in which the source code isn't available
to you, or when your support agreements would no longer be valid if you made
any changes directly to the code.