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

The OPTIMIZE FOR hint enables you to tell the Query Optimizer what values you expect to see most
commonly at runtime. Provided that the values you specify are the most common case, this can
result in better performance for the majority of the queries, or at least those that match the case for
which you optimized.
RECOMPILE
The RECOMPILE query hint is a more granular way to force recompilation in a stored procedure to be
at the statement level rather than using the WITH RECOMPILE option, which forces the whole stored
procedure to be recompiled.
When the Query Optimizer sees the RECOMPILE query hint, it forces a new query plan to be created
regardless of what plans may already be cached. The new plan is created with the parameters within
the current execution context.
This is a very useful option if you know that a particular part of a stored procedure has very
different input parameters that can affect the resulting query plan dramatically. Using this
option may incur a small cost for the compilation needed on every execution, but if that's a small
percentage of the resulting query's execution time, it's a worthwhile cost to pay to ensure that every
execution of the query gets the most optimal plan.
For cases in which the additional compilation cost is high relative to the cost of the worst execution,
using this query hint would be detrimental to performance.
USE PLAN N 'xml plan'
The USE PLAN query hint tells the Query Optimizer that you want a new plan, and that the new plan
should match the shape of the plan in the supplied XML plan.
This is very similar to the use of plan guides (covered in the next section), but whereas plan guides
don't require a change to the query, the USE PLAN query hint does require a change to the T-SQL
being submitted to the server.
Sometimes this query hint is used to solve deadlock issues or other data-related problems. However,
in nearly all cases the correct course of action is to address the underlying issue, but that often
involves architectural changes, or code changes that require extensive development and test work to
get into production. In these cases the USE PLAN query hint can provide a quick workaround for the
DBA to keep the system running while the root cause of a problem is found and i xed.
Note that the preceding course of action assumes you have a “good” XML plan from the prob-
lem query that doesn't show the problem behavior. If you just happened to capture a bunch of
XML plans from all the queries running on your system when it was working well, then you are
good to go, but that's not typically something that anyone ever does, as you usually leave systems
alone when they are working OK; and capturing XML plans for every query running today just in
case you may want to use the USE PLAN query hint at some point in the future is not a very useful
practice.
What you may be able to do, however, is coni gure a test system with data such that the plan your
target query generates is of the desired shape, capture the XML for the plan, and use that XML
plan to “i x” the plan's shape on your production server.