Databases Reference
In-Depth Information
This can be the case, for example, when a better performing plan was produced in the
past, or in a different system, or even in a previous version of SQL Server.
The plan should be specified in XML format, and you will usually use SQL Server itself
to generate the XML text for the desired plan, as it can be extremely difficult to write an
XML plan manually.
The
USE
PLAN
hint can force most of the specified plan properties, including the tree
structure, join order, join algorithms, aggregations, sorting and unions, and index
operations like scans, seeks and intersections, so that only the transformation rules
that can be useful in finding the desired plan are executed. In addition,
USE
PLAN
now
supports
UPDATE
statements (
INSERT
,
UPDATE
,
DELETE
and
MERGE
), which was not
the case when the hint was first introduced in SQL Server 2005. Some statements still
not supported include full-text or distributed queries, and queries with dynamic, keyset-
driven and forward-only cursors.
Suppose we have the same query we saw in the plan guides section, which produces a
Hash Join ...
SELECT
FirstName
,
LastName
FROM
Person
.
Contact
AS
C
JOIN
Sales
.
Individual
AS
I
ON
C
.
ContactID
=
I
.
ContactID
Listing 7-37.
… and suppose that you want SQL Server to use a different execution plan, which we can
generate using a hint:
SELECT
FirstName
,
LastName
FROM
Person
.
Contact
AS
C
JOIN
Sales
.
Individual
AS
I
ON
C
.
ContactID
=
I
.
ContactID
OPTION
(
LOOP
JOIN)
Listing 7-38.
