Databases Reference
In-Depth Information
select the blank template to start a new trace definition, and select both
Plan
Guide
Successful
and
Plan
Guide
Unsuccessful
on the
Performance
section of the
Events
tab, and then start the trace.
Next, create the following stored procedure:
CREATE
PROCEDURE
test
AS
SELECT
FirstName
,
LastName
FROM
Person
.
Contact
AS
C
JOIN
Sales
.
Individual
AS
I
ON
C
.
ContactID
=
I
.
ContactID
Listing 7-31.
Before creating a plan guide, execute the stored procedure and display its execution plan
to verify that it is using a Hash Join operator.
EXEC
test
Listing 7-32.
Once you've confirmed that, create a plan guide to force the query to use a Nested
Loops Join.
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 (LOOP JOIN)'
;
Listing 7-33.
