Databases Reference
In-Depth Information
You can force this new plan to use a Nested Loops Join instead of a Hash Join. In order
to accomplish that, display the new XML plan (by right-clicking on the graphical plan
and selecting
Show Execution Plan XML …
), copy it to an editor, replace all of the single
quotes with double quotes, and then copy the plan to the query, as shown below.
SELECT
FirstName
,
LastName
FROM
Person
.
Contact
AS
C
JOIN
Sales
.
Individual
AS
I
ON
C
.
ContactID
=
I
.
ContactID
OPTION
(
USE
PLAN
N'<?xml version="1.0" encoding="utf-16"?> …
</ShowPlanXML>'
)
Listing 7-39.
Of course, the XML plan is too long to display here, so I've just displayed the start and
end. Make sure the query ends with
')
after the XML plan. Running the
SELECT
state-
ment above will request SQL Server to try to use the indicated plan, and the query will be
executed with a Nested Loops Join, as requested in the provided XML execution plan.
You can combine both plan guides and the
USE
PLAN
query hint to force a specific execu-
tion plan in a situation where you don't want to change the text of the original query. The
following (and final) query will use the same example included in Listing 7-33 in the plan
guides section, together with the XML plan generated a moment ago. Note the use of two
single quotes before the XML plan specification, meaning that, this time, the query text
needs to end with
'')'
.
EXEC
sp_create_plan_guide
@name
=
N'plan_guide_test'
,
@stmt
=
N'SELECT FirstName, LastName
FROM Person.Contact AS C JOIN Sales.Individual AS I
ON C.ContactID = I.ContactID'
,
@type
=
N'OBJECT'
,
@module_or_batch
=
N'test'
,
@params
=
NULL,
@hints
=
N'OPTION (USE PLAN N''<?xml version="1.0" encoding="utf-16"?> …
Listing 7-40.
