
USE AdventureWorks
GO

-- After capturing the STATISTICS XML query plan for the rewritten query, 
-- and testing it on the original query, create a plan guide 
-- to force the plan on the original query, as shown in the following code:

-- This works, but you must replace quotes with 
-- 2 quotes in the statement and 4 quotes in the plan!

EXEC sp_create_plan_guide
@name = N'ForceOrderGuide',
@stmt = N'SELECT 
    soh.[SalesPersonID]
    ,c.[FirstName] + '' '' + COALESCE(c.[MiddleName], '''') + '' '' + c.[LastName] AS [FullName]
    ,e.[Title]
    ,st.[Name] AS [SalesTerritory]
    ,soh.[SubTotal]
    ,YEAR(DATEADD(m, 6, soh.[OrderDate])) AS [FiscalYear] 
FROM [Sales].[SalesPerson] sp 
    INNER JOIN [Sales].[SalesOrderHeader] soh 
    ON sp.[SalesPersonID] = soh.[SalesPersonID]
    INNER JOIN [Sales].[SalesTerritory] st 
    ON sp.[TerritoryID] = st.[TerritoryID] 
    INNER JOIN [HumanResources].[Employee] e 
    ON soh.[SalesPersonID] = e.[EmployeeID] 
    INNER JOIN [Person].[Contact] c 
    ON e.[ContactID] = c.ContactID
WHERE st.[Group] = @p1', 
@type = N'SQL',
@module_or_batch = NULL,
@params = N'@p1 nvarchar(80)',
@hints = N'OPTION (USE PLAN 
N''<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.0" Build="9.00.1399.06">
  <BatchSequence>
    <Batch>
      <Statements>
        <StmtSimple StatementText="SELECT &#xD;&#xA;    soh.[SalesPersonID]&#xD;&#xA;    ,c.[FirstName] + '''' '''' + COALESCE(c.[MiddleName], '''''''') + '''' '''' + c.[LastName] AS [FullName]&#xD;&#xA;    ,e.[Title]&#xD;&#xA;    ,st.[Name] AS [SalesTerritory]&#xD;&#xA;    ,soh.[SubTotal]&#xD;&#xA;    ,YEAR(DATEADD(m, 6, soh.[OrderDate])) AS [FiscalYear] &#xD;&#xA;FROM [Sales].[SalesPerson] sp &#xD;&#xA;    INNER JOIN [Sales].[SalesTerritory] st -- Moved this join earlier &#xD;&#xA;    ON sp.[TerritoryID] = st.[TerritoryID] &#xD;&#xA;    INNER JOIN [Sales].[SalesOrderHeader] soh &#xD;&#xA;    ON sp.[SalesPersonID] = soh.[SalesPersonID]&#xD;&#xA;    INNER JOIN [HumanResources].[Employee] e &#xD;&#xA;    ON soh.[SalesPersonID] = e.[EmployeeID] &#xD;&#xA;    INNER JOIN [Person].[Contact] c &#xD;&#xA;    ON e.[ContactID] = c.ContactID&#xD;&#xA;WHERE st.[Group] = @p1&#xD;&#xA;OPTION (FORCE ORDER)" StatementId="1" StatementCompId="2" StatementType="SELECT" StatementSubTreeCost="0.901886" StatementEstRows="195.31" StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="GoodEnoughPlanFound">
          <StatementSetOptions QUOTED_IDENTIFIER="false" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="false" ANSI_NULLS="false" ANSI_PADDING="false" ANSI_WARNINGS="false" NUMERIC_ROUNDABORT="false" />
          <QueryPlan DegreeOfParallelism="0" MemoryGrant="102" CachedPlanSize="57">
            <RelOp NodeId="0" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="195.31" EstimateIO="0" EstimateCPU="1.9531e-005" AvgRowSize="283" EstimatedTotalSubtreeCost="0.901886" Parallel="0" EstimateRebinds="0" EstimateRewinds="0">
              <OutputList>
                <ColumnReference Database="[AdventureWorks]" Schema="[Sales]" Table="[SalesTerritory]" Alias="[st]" Column="Name" />
                <ColumnReference Database="[AdventureWorks]" Schema="[Sales]" Table="[SalesOrderHeader]" Alias="[soh]" Column="SalesPersonID" />
                <ColumnReference Database="[AdventureWorks]" Schema="[Sales]" Table="[SalesOrderHeader]" Alias="[soh]" Column="SubTotal" />
                <ColumnReference Database="[AdventureWorks]" Schema="[HumanResources]" Table="[Employee]" Alias="[e]" Column="Title" />
                <ColumnReference Column="Expr1010" />
                <ColumnReference Column="Expr1011" />
              </OutputList>
              <ComputeScalar>
                <DefinedValues>
                  <DefinedValue>
                    <ColumnReference Column="Expr1010" />
                    <ScalarOperator ScalarString="((([AdventureWorks].[Person].[Contact].[FirstName] as [c].[FirstName]+N'''' '''')+CASE WHEN [AdventureWorks].[Person].[Contact].[MiddleName] as [c].[MiddleName] IS NOT NULL THEN [AdventureWorks].[Person].[Contact].[MiddleName] as [c].[MiddleName] ELSE N'''''''' END)+N'''' '''')+[AdventureWorks].[Person].[Contact].[LastName] as [c].[LastName]">
                      <Arithmetic Operation="ADD">
                        <ScalarOperator>
                          <Arithmetic Operation="ADD">
                            <ScalarOperator>
                              <Arithmetic Operation="ADD">
                                <ScalarOperator>
                                  <Arithmetic Operation="ADD">
                                    <ScalarOperator>
                                      <Identifier>
                                        <ColumnReference Database="[AdventureWorks]" Schema="[Person]" Table="[Contact]" Alias="[c]" Column="FirstName" />
                                      </Identifier>
                                    </ScalarOperator>
                                    <ScalarOperator>
                                      <Const ConstValue="N'''' ''''" />
                                    </ScalarOperator>
                                  </Arithmetic>
                                </ScalarOperator>
                                <ScalarOperator>
                                  <IF>
                                    <Condition>
                                      <ScalarOperator>
                                        <Compare CompareOp="IS NOT">
                                          <ScalarOperator>
                                            <Identifier>
                                              <ColumnReference Database="[AdventureWorks]" Schema="[Person]" Table="[Contact]" Alias="[c]" Column="MiddleName" />
                                            </Identifier>
                                          </ScalarOperator>
                                          <ScalarOperator>
                                            <Const ConstValue="NULL" />
                                          </ScalarOperator>
                                        </Compare>
                                      </ScalarOperator>
                                    </Condition>
                                    <Then>
                                      <ScalarOperator>
                                        <Identifier>
                                          <ColumnReference Database="[AdventureWorks]" Schema="[Person]" Table="[Contact]" Alias="[c]" Column="MiddleName" />
                                        </Identifier>
                                      </ScalarOperator>
                                    </Then>
                                    <Else>
                                      <ScalarOperator>
                                        <Const ConstValue="N''''''''" />
                                      </ScalarOperator>
                                    </Else>
                                  </IF>
                                </ScalarOperator>
                              </Arithmetic>
                            </ScalarOperator>
                            <ScalarOperator>
                              <Const ConstValue="N'''' ''''" />
                            </ScalarOperator>
                          </Arithmetic>
                        </ScalarOperator>
                        <ScalarOperator>
                          <Identifier>
                            <ColumnReference Database="[AdventureWorks]" Schema="[Person]" Table="[Contact]" Alias="[c]" Column="LastName" />
                          </Identifier>
                        </ScalarOperator>
                      </Arithmetic>
                    </ScalarOperator>
                  </DefinedValue>
                  <DefinedValue>
                    <ColumnReference Column="Expr1011" />
                    <ScalarOperator ScalarString="datepart(year,dateadd(month,(6),[AdventureWorks].[Sales].[SalesOrderHeader].[OrderDate] as [soh].[OrderDate]))">
                      <Intrinsic FunctionName="datepart">
                        <ScalarOperator>
                          <Const ConstValue="(0)" />
                        </ScalarOperator>
                        <ScalarOperator>
                          <Intrinsic FunctionName="dateadd">
                            <ScalarOperator>
                              <Const ConstValue="(2)" />
                            </ScalarOperator>
                            <ScalarOperator>
                              <Const ConstValue="(6)" />
                            </ScalarOperator>
                            <ScalarOperator>
                              <Identifier>
                                <ColumnReference Database="[AdventureWorks]" Schema="[Sales]" Table="[SalesOrderHeader]" Alias="[soh]" Column="OrderDate" />
                              </Identifier>
                            </ScalarOperator>
                          </Intrinsic>
                        </ScalarOperator>
                      </Intrinsic>
                    </ScalarOperator>
                  </DefinedValue>
                </DefinedValues>
                <RelOp NodeId="1" PhysicalOp="Nested Loops" LogicalOp="Inner Join" EstimateRows="195.31" EstimateIO="0" EstimateCPU="0.000816395" AvgRowSize="289" EstimatedTotalSubtreeCost="0.901867" Parallel="0" EstimateRebinds="0" EstimateRewinds="0">
                  <OutputList>
                    <ColumnReference Database="[AdventureWorks]" Schema="[Sales]" Table="[SalesTerritory]" Alias="[st]" Column="Name" />
                    <ColumnReference Database="[AdventureWorks]" Schema="[Sales]" Table="[SalesOrderHeader]" Alias="[soh]" Column="OrderDate" />
                    <ColumnReference Database="[AdventureWorks]" Schema="[Sales]" Table="[SalesOrderHeader]" Alias="[soh]" Column="SalesPersonID" />
                    <ColumnReference Database="[AdventureWorks]" Schema="[Sales]" Table="[SalesOrderHeader]" Alias="[soh]" Column="SubTotal" />
                    <ColumnReference Database="[AdventureWorks]" Schema="[HumanResources]" Table="[Employee]" Alias="[e]" Column="Title" />
                    <ColumnReference Database="[AdventureWorks]" Schema="[Person]" Table="[Contact]" Alias="[c]" Column="FirstName" />
                    <ColumnReference Database="[AdventureWorks]" Schema="[Person]" Table="[Contact]" Alias="[c]" Column="MiddleName" />
                    <ColumnReference Database="[AdventureWorks]" Schema="[Person]" Table="[Contact]" Alias="[c]" Column="LastName" />
                  </OutputList>
                  <RunTimeInformation>
                    <RunTimeCountersPerThread Thread="0" ActualRows="2941" ActualEndOfScans="1" ActualExecutions="1" />
                  </RunTimeInformation>
                  <NestedLoops Optimized="0" WithUnorderedPrefetch="1">
                    <OuterReferences>
                      <ColumnReference Database="[AdventureWorks]" Schema="[HumanResources]" Table="[Employee]" Alias="[e]" Column="ContactID" />
                      <ColumnReference Column="Expr1013" />
                    </OuterReferences>
                    <RelOp NodeId="3" PhysicalOp="Merge Join" LogicalOp="Inner Join" EstimateRows="195.31" EstimateIO="0.000939" EstimateCPU="0.010045" AvgRowSize="137" EstimatedTotalSubtreeCost="0.8469" Parallel="0" EstimateRebinds="0" EstimateRewinds="0">
                      <OutputList>
                        <ColumnReference Database="[AdventureWorks]" Schema="[Sales]" Table="[SalesTerritory]" Alias="[st]" Column="Name" />
                        <ColumnReference Database="[AdventureWorks]" Schema="[Sales]" Table="[SalesOrderHeader]" Alias="[soh]" Column="OrderDate" />
                        <ColumnReference Database="[AdventureWorks]" Schema="[Sales]" Table="[SalesOrderHeader]" Alias="[soh]" Column="SalesPersonID" />
                        <ColumnReference Database="[AdventureWorks]" Schema="[Sales]" Table="[SalesOrderHeader]" Alias="[soh]" Column="SubTotal" />
                        <ColumnReference Database="[AdventureWorks]" Schema="[HumanResources]" Table="[Employee]" Alias="[e]" Column="ContactID" />
                        <ColumnReference Database="[AdventureWorks]" Schema="[HumanResources]" Table="[Employee]" Alias="[e]" Column="Title" />
                      </OutputList>
                      <RunTimeInformation>
                        <RunTimeCountersPerThread Thread="0" ActualRows="2941" ActualEndOfScans="1" ActualExecutions="1" />
                      </RunTimeInformation>
                      <Merge ManyToMany="1">
                        <InnerSideJoinColumns>
                          <ColumnReference Database="[AdventureWorks]" Schema="[HumanResources]" Table="[Employee]" Alias="[e]" Column="EmployeeID" />
                        </InnerSideJoinColumns>
                        <OuterSideJoinColumns>
                          <ColumnReference Database="[AdventureWorks]" Schema="[Sales]" Table="[SalesOrderHeader]" Alias="[soh]" Column="SalesPersonID" />
                        </OuterSideJoinColumns>
                        <Residual>
                          <ScalarOperator ScalarString="[AdventureWorks].[Sales].[SalesOrderHeader].[SalesPersonID] as [soh].[SalesPersonID]=[AdventureWorks].[HumanResources].[Employee].[EmployeeID] as [e].[EmployeeID]">
                            <Compare CompareOp="EQ">
                              <ScalarOperator>
                                <Identifier>
                                  <ColumnReference Database="[AdventureWorks]" Schema="[Sales]" Table="[SalesOrderHeader]" Alias="[soh]" Column="SalesPersonID" />
                                </Identifier>
                              </ScalarOperator>
                              <ScalarOperator>
                                <Identifier>
                                  <ColumnReference Database="[AdventureWorks]" Schema="[HumanResources]" Table="[Employee]" Alias="[e]" Column="EmployeeID" />
                                </Identifier>
                              </ScalarOperator>
                            </Compare>
                          </ScalarOperator>
                        </Residual>
                        <RelOp NodeId="4" PhysicalOp="Sort" LogicalOp="Sort" EstimateRows="1614.67" EstimateIO="0.0112613" EstimateCPU="0.0269438" AvgRowSize="85" EstimatedTotalSubtreeCost="0.827867" Parallel="0" EstimateRebinds="0" EstimateRewinds="0">
                          <OutputList>
                            <ColumnReference Database="[AdventureWorks]" Schema="[Sales]" Table="[SalesPerson]" Alias="[sp]" Column="SalesPersonID" />
                            <ColumnReference Database="[AdventureWorks]" Schema="[Sales]" Table="[SalesTerritory]" Alias="[st]" Column="Name" />
                            <ColumnReference Database="[AdventureWorks]" Schema="[Sales]" Table="[SalesOrderHeader]" Alias="[soh]" Column="OrderDate" />
                            <ColumnReference Database="[AdventureWorks]" Schema="[Sales]" Table="[SalesOrderHeader]" Alias="[soh]" Column="SalesPersonID" />
                            <ColumnReference Database="[AdventureWorks]" Schema="[Sales]" Table="[SalesOrderHeader]" Alias="[soh]" Column="SubTotal" />
                          </OutputList>
                          <MemoryFractions Input="1" Output="1" />
                          <RunTimeInformation>
                            <RunTimeCountersPerThread Thread="0" ActualRows="2941" ActualRebinds="1" ActualRewinds="0" ActualEndOfScans="1" ActualExecutions="1" />
                          </RunTimeInformation>
                          <Sort Distinct="0">
                            <OrderBy>
                              <OrderByColumn Ascending="1">
                                <ColumnReference Database="[AdventureWorks]" Schema="[Sales]" Table="[SalesPerson]" Alias="[sp]" Column="SalesPersonID" />
                              </OrderByColumn>
                            </OrderBy>
                            <RelOp NodeId="5" PhysicalOp="Hash Match" LogicalOp="Inner Join" EstimateRows="1614.67" EstimateIO="0" EstimateCPU="0.225536" AvgRowSize="85" EstimatedTotalSubtreeCost="0.789662" Parallel="0" EstimateRebinds="0" EstimateRewinds="0">
                              <OutputList>
                                <ColumnReference Database="[AdventureWorks]" Schema="[Sales]" Table="[SalesPerson]" Alias="[sp]" Column="SalesPersonID" />
                                <ColumnReference Database="[AdventureWorks]" Schema="[Sales]" Table="[SalesTerritory]" Alias="[st]" Column="Name" />
                                <ColumnReference Database="[AdventureWorks]" Schema="[Sales]" Table="[SalesOrderHeader]" Alias="[soh]" Column="OrderDate" />
                                <ColumnReference Database="[AdventureWorks]" Schema="[Sales]" Table="[SalesOrderHeader]" Alias="[soh]" Column="SalesPersonID" />
                                <ColumnReference Database="[AdventureWorks]" Schema="[Sales]" Table="[SalesOrderHeader]" Alias="[soh]" Column="SubTotal" />
                              </OutputList>
                              <MemoryFractions Input="0" Output="0" />
                              <RunTimeInformation>
                                <RunTimeCountersPerThread Thread="0" ActualRows="2941" ActualEndOfScans="1" ActualExecutions="1" />
                              </RunTimeInformation>
                              <Hash>
                                <DefinedValues />
                                <HashKeysBuild>
                                  <ColumnReference Database="[AdventureWorks]" Schema="[Sales]" Table="[SalesPerson]" Alias="[sp]" Column="SalesPersonID" />
                                </HashKeysBuild>
                                <HashKeysProbe>
                                  <ColumnReference Database="[AdventureWorks]" Schema="[Sales]" Table="[SalesOrderHeader]" Alias="[soh]" Column="SalesPersonID" />
                                </HashKeysProbe>
                                <ProbeResidual>
                                  <ScalarOperator ScalarString="[AdventureWorks].[Sales].[SalesOrderHeader].[SalesPersonID] as [soh].[SalesPersonID]=[AdventureWorks].[Sales].[SalesPerson].[SalesPersonID] as [sp].[SalesPersonID]">
                                    <Compare CompareOp="EQ">
                                      <ScalarOperator>
                                        <Identifier>
                                          <ColumnReference Database="[AdventureWorks]" Schema="[Sales]" Table="[SalesOrderHeader]" Alias="[soh]" Column="SalesPersonID" />
                                        </Identifier>
                                      </ScalarOperator>
                                      <ScalarOperator>
                                        <Identifier>
                                          <ColumnReference Database="[AdventureWorks]" Schema="[Sales]" Table="[SalesPerson]" Alias="[sp]" Column="SalesPersonID" />
                                        </Identifier>
                                      </ScalarOperator>
                                    </Compare>
                                  </ScalarOperator>
                                </ProbeResidual>
                                <RelOp NodeId="6" PhysicalOp="Nested Loops" LogicalOp="Inner Join" EstimateRows="7.63636" EstimateIO="0" EstimateCPU="7.106e-005" AvgRowSize="65" EstimatedTotalSubtreeCost="0.00919262" Parallel="0" EstimateRebinds="0" EstimateRewinds="0">
                                  <OutputList>
                                    <ColumnReference Database="[AdventureWorks]" Schema="[Sales]" Table="[SalesPerson]" Alias="[sp]" Column="SalesPersonID" />
                                    <ColumnReference Database="[AdventureWorks]" Schema="[Sales]" Table="[SalesTerritory]" Alias="[st]" Column="Name" />
                                  </OutputList>
                                  <RunTimeInformation>
                                    <RunTimeCountersPerThread Thread="0" ActualRows="10" ActualEndOfScans="1" ActualExecutions="1" />
                                  </RunTimeInformation>
                                  <NestedLoops Optimized="0">
                                    <OuterReferences>
                                      <ColumnReference Database="[AdventureWorks]" Schema="[Sales]" Table="[SalesPerson]" Alias="[sp]" Column="TerritoryID" />
                                    </OuterReferences>
                                    <RelOp NodeId="7" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="17" EstimateIO="0.003125" EstimateCPU="0.0001757" AvgRowSize="15" EstimatedTotalSubtreeCost="0.0033007" Parallel="0" EstimateRebinds="0" EstimateRewinds="0">
                                      <OutputList>
                                        <ColumnReference Database="[AdventureWorks]" Schema="[Sales]" Table="[SalesPerson]" Alias="[sp]" Column="SalesPersonID" />
                                        <ColumnReference Database="[AdventureWorks]" Schema="[Sales]" Table="[SalesPerson]" Alias="[sp]" Column="TerritoryID" />
                                      </OutputList>
                                      <RunTimeInformation>
                                        <RunTimeCountersPerThread Thread="0" ActualRows="17" ActualEndOfScans="1" ActualExecutions="1" />
                                      </RunTimeInformation>
                                      <IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" NoExpandHint="0">
                                        <DefinedValues>
                                          <DefinedValue>
                                            <ColumnReference Database="[AdventureWorks]" Schema="[Sales]" Table="[SalesPerson]" Alias="[sp]" Column="SalesPersonID" />
                                          </DefinedValue>
                                          <DefinedValue>
                                            <ColumnReference Database="[AdventureWorks]" Schema="[Sales]" Table="[SalesPerson]" Alias="[sp]" Column="TerritoryID" />
                                          </DefinedValue>
                                        </DefinedValues>
                                        <Object Database="[AdventureWorks]" Schema="[Sales]" Table="[SalesPerson]" Index="[PK_SalesPerson_SalesPersonID]" Alias="[sp]" />
                                      </IndexScan>
                                    </RelOp>
                                    <RelOp NodeId="8" PhysicalOp="Clustered Index Seek" LogicalOp="Clustered Index Seek" EstimateRows="1" EstimateIO="0.003125" EstimateCPU="0.0001581" AvgRowSize="83" EstimatedTotalSubtreeCost="0.0058127" Parallel="0" EstimateRebinds="15.4118" EstimateRewinds="0.588235">
                                      <OutputList>
                                        <ColumnReference Database="[AdventureWorks]" Schema="[Sales]" Table="[SalesTerritory]" Alias="[st]" Column="Name" />
                                        <ColumnReference Database="[AdventureWorks]" Schema="[Sales]" Table="[SalesTerritory]" Alias="[st]" Column="Group" />
                                      </OutputList>
                                      <RunTimeInformation>
                                        <RunTimeCountersPerThread Thread="0" ActualRows="10" ActualEndOfScans="7" ActualExecutions="17" />
                                      </RunTimeInformation>
                                      <IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" NoExpandHint="0">
                                        <DefinedValues>
                                          <DefinedValue>
                                            <ColumnReference Database="[AdventureWorks]" Schema="[Sales]" Table="[SalesTerritory]" Alias="[st]" Column="Name" />
                                          </DefinedValue>
                                          <DefinedValue>
                                            <ColumnReference Database="[AdventureWorks]" Schema="[Sales]" Table="[SalesTerritory]" Alias="[st]" Column="Group" />
                                          </DefinedValue>
                                        </DefinedValues>
                                        <Object Database="[AdventureWorks]" Schema="[Sales]" Table="[SalesTerritory]" Index="[PK_SalesTerritory_TerritoryID]" Alias="[st]" />
                                        <SeekPredicates>
                                          <SeekPredicate>
                                            <Prefix ScanType="EQ">
                                              <RangeColumns>
                                                <ColumnReference Database="[AdventureWorks]" Schema="[Sales]" Table="[SalesTerritory]" Alias="[st]" Column="TerritoryID" />
                                              </RangeColumns>
                                              <RangeExpressions>
                                                <ScalarOperator ScalarString="[AdventureWorks].[Sales].[SalesPerson].[TerritoryID] as [sp].[TerritoryID]">
                                                  <Identifier>
                                                    <ColumnReference Database="[AdventureWorks]" Schema="[Sales]" Table="[SalesPerson]" Alias="[sp]" Column="TerritoryID" />
                                                  </Identifier>
                                                </ScalarOperator>
                                              </RangeExpressions>
                                            </Prefix>
                                          </SeekPredicate>
                                        </SeekPredicates>
                                        <Predicate>
                                          <ScalarOperator ScalarString="[AdventureWorks].[Sales].[SalesTerritory].[Group] as [st].[Group]=[@p1]">
                                            <Compare CompareOp="EQ">
                                              <ScalarOperator>
                                                <Identifier>
                                                  <ColumnReference Database="[AdventureWorks]" Schema="[Sales]" Table="[SalesTerritory]" Alias="[st]" Column="Group" />
                                                </Identifier>
                                              </ScalarOperator>
                                              <ScalarOperator>
                                                <Identifier>
                                                  <ColumnReference Column="@p1" />
                                                </Identifier>
                                              </ScalarOperator>
                                            </Compare>
                                          </ScalarOperator>
                                        </Predicate>
                                      </IndexScan>
                                    </RelOp>
                                  </NestedLoops>
                                </RelOp>
                                <RelOp NodeId="9" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="31465" EstimateIO="0.520162" EstimateCPU="0.0347685" AvgRowSize="27" EstimatedTotalSubtreeCost="0.554931" Parallel="0" EstimateRebinds="0" EstimateRewinds="0">
                                  <OutputList>
                                    <ColumnReference Database="[AdventureWorks]" Schema="[Sales]" Table="[SalesOrderHeader]" Alias="[soh]" Column="OrderDate" />
                                    <ColumnReference Database="[AdventureWorks]" Schema="[Sales]" Table="[SalesOrderHeader]" Alias="[soh]" Column="SalesPersonID" />
                                    <ColumnReference Database="[AdventureWorks]" Schema="[Sales]" Table="[SalesOrderHeader]" Alias="[soh]" Column="SubTotal" />
                                  </OutputList>
                                  <RunTimeInformation>
                                    <RunTimeCountersPerThread Thread="0" ActualRows="31465" ActualEndOfScans="1" ActualExecutions="1" />
                                  </RunTimeInformation>
                                  <IndexScan Ordered="0" ForcedIndex="0" NoExpandHint="0">
                                    <DefinedValues>
                                      <DefinedValue>
                                        <ColumnReference Database="[AdventureWorks]" Schema="[Sales]" Table="[SalesOrderHeader]" Alias="[soh]" Column="OrderDate" />
                                      </DefinedValue>
                                      <DefinedValue>
                                        <ColumnReference Database="[AdventureWorks]" Schema="[Sales]" Table="[SalesOrderHeader]" Alias="[soh]" Column="SalesPersonID" />
                                      </DefinedValue>
                                      <DefinedValue>
                                        <ColumnReference Database="[AdventureWorks]" Schema="[Sales]" Table="[SalesOrderHeader]" Alias="[soh]" Column="SubTotal" />
                                      </DefinedValue>
                                    </DefinedValues>
                                    <Object Database="[AdventureWorks]" Schema="[Sales]" Table="[SalesOrderHeader]" Index="[PK_SalesOrderHeader_SalesOrderID]" Alias="[soh]" />
                                  </IndexScan>
                                </RelOp>
                              </Hash>
                            </RelOp>
                          </Sort>
                        </RelOp>
                        <RelOp NodeId="11" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="290" EstimateIO="0.00756944" EstimateCPU="0.000476" AvgRowSize="69" EstimatedTotalSubtreeCost="0.00804544" Parallel="0" EstimateRebinds="0" EstimateRewinds="0">
                          <OutputList>
                            <ColumnReference Database="[AdventureWorks]" Schema="[HumanResources]" Table="[Employee]" Alias="[e]" Column="EmployeeID" />
                            <ColumnReference Database="[AdventureWorks]" Schema="[HumanResources]" Table="[Employee]" Alias="[e]" Column="ContactID" />
                            <ColumnReference Database="[AdventureWorks]" Schema="[HumanResources]" Table="[Employee]" Alias="[e]" Column="Title" />
                          </OutputList>
                          <RunTimeInformation>
                            <RunTimeCountersPerThread Thread="0" ActualRows="288" ActualEndOfScans="0" ActualExecutions="1" />
                          </RunTimeInformation>
                          <IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" NoExpandHint="0">
                            <DefinedValues>
                              <DefinedValue>
                                <ColumnReference Database="[AdventureWorks]" Schema="[HumanResources]" Table="[Employee]" Alias="[e]" Column="EmployeeID" />
                              </DefinedValue>
                              <DefinedValue>
                                <ColumnReference Database="[AdventureWorks]" Schema="[HumanResources]" Table="[Employee]" Alias="[e]" Column="ContactID" />
                              </DefinedValue>
                              <DefinedValue>
                                <ColumnReference Database="[AdventureWorks]" Schema="[HumanResources]" Table="[Employee]" Alias="[e]" Column="Title" />
                              </DefinedValue>
                            </DefinedValues>
                            <Object Database="[AdventureWorks]" Schema="[HumanResources]" Table="[Employee]" Index="[PK_Employee_EmployeeID]" Alias="[e]" />
                          </IndexScan>
                        </RelOp>
                      </Merge>
                    </RelOp>
                    <RelOp NodeId="13" PhysicalOp="Clustered Index Seek" LogicalOp="Clustered Index Seek" EstimateRows="1" EstimateIO="0.003125" EstimateCPU="0.0001581" AvgRowSize="165" EstimatedTotalSubtreeCost="0.0541507" Parallel="0" EstimateRebinds="184.527" EstimateRewinds="9.78261">
                      <OutputList>
                        <ColumnReference Database="[AdventureWorks]" Schema="[Person]" Table="[Contact]" Alias="[c]" Column="FirstName" />
                        <ColumnReference Database="[AdventureWorks]" Schema="[Person]" Table="[Contact]" Alias="[c]" Column="MiddleName" />
                        <ColumnReference Database="[AdventureWorks]" Schema="[Person]" Table="[Contact]" Alias="[c]" Column="LastName" />
                      </OutputList>
                      <RunTimeInformation>
                        <RunTimeCountersPerThread Thread="0" ActualRows="2941" ActualEndOfScans="0" ActualExecutions="2941" />
                      </RunTimeInformation>
                      <IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" NoExpandHint="0">
                        <DefinedValues>
                          <DefinedValue>
                            <ColumnReference Database="[AdventureWorks]" Schema="[Person]" Table="[Contact]" Alias="[c]" Column="FirstName" />
                          </DefinedValue>
                          <DefinedValue>
                            <ColumnReference Database="[AdventureWorks]" Schema="[Person]" Table="[Contact]" Alias="[c]" Column="MiddleName" />
                          </DefinedValue>
                          <DefinedValue>
                            <ColumnReference Database="[AdventureWorks]" Schema="[Person]" Table="[Contact]" Alias="[c]" Column="LastName" />
                          </DefinedValue>
                        </DefinedValues>
                        <Object Database="[AdventureWorks]" Schema="[Person]" Table="[Contact]" Index="[PK_Contact_ContactID]" Alias="[c]" />
                        <SeekPredicates>
                          <SeekPredicate>
                            <Prefix ScanType="EQ">
                              <RangeColumns>
                                <ColumnReference Database="[AdventureWorks]" Schema="[Person]" Table="[Contact]" Alias="[c]" Column="ContactID" />
                              </RangeColumns>
                              <RangeExpressions>
                                <ScalarOperator ScalarString="[AdventureWorks].[HumanResources].[Employee].[ContactID] as [e].[ContactID]">
                                  <Identifier>
                                    <ColumnReference Database="[AdventureWorks]" Schema="[HumanResources]" Table="[Employee]" Alias="[e]" Column="ContactID" />
                                  </Identifier>
                                </ScalarOperator>
                              </RangeExpressions>
                            </Prefix>
                          </SeekPredicate>
                        </SeekPredicates>
                      </IndexScan>
                    </RelOp>
                  </NestedLoops>
                </RelOp>
              </ComputeScalar>
            </RelOp>
            <ParameterList>
              <ColumnReference Column="@p1" ParameterCompiledValue="N''''North America''''" ParameterRuntimeValue="N''''North America''''" />
            </ParameterList>
          </QueryPlan>
        </StmtSimple>
      </Statements>
    </Batch>
  </BatchSequence>
</ShowPlanXML>'')'
GO

sp_control_plan_guide N'ENABLE', N'ForceOrderGuide'
GO

sp_executesql 
N'SELECT 
    soh.[SalesPersonID]
    ,c.[FirstName] + '' '' + COALESCE(c.[MiddleName], '''') + '' '' + c.[LastName] AS [FullName]
    ,e.[Title]
    ,st.[Name] AS [SalesTerritory]
    ,soh.[SubTotal]
    ,YEAR(DATEADD(m, 6, soh.[OrderDate])) AS [FiscalYear] 
FROM [Sales].[SalesPerson] sp 
    INNER JOIN [Sales].[SalesOrderHeader] soh 
    ON sp.[SalesPersonID] = soh.[SalesPersonID]
    INNER JOIN [Sales].[SalesTerritory] st 
    ON sp.[TerritoryID] = st.[TerritoryID] 
    INNER JOIN [HumanResources].[Employee] e 
    ON soh.[SalesPersonID] = e.[EmployeeID] 
    INNER JOIN [Person].[Contact] c 
    ON e.[ContactID] = c.ContactID
WHERE st.[Group] = @p1', N'@p1 nvarchar(80)', N'Europe' 

sp_control_plan_guide N'DISABLE', N'ForceOrderGuide'
GO

sp_executesql 
N'SELECT 
    soh.[SalesPersonID]
    ,c.[FirstName] + '' '' + COALESCE(c.[MiddleName], '''') + '' '' + c.[LastName] AS [FullName]
    ,e.[Title]
    ,st.[Name] AS [SalesTerritory]
    ,soh.[SubTotal]
    ,YEAR(DATEADD(m, 6, soh.[OrderDate])) AS [FiscalYear] 
FROM [Sales].[SalesPerson] sp 
    INNER JOIN [Sales].[SalesOrderHeader] soh 
    ON sp.[SalesPersonID] = soh.[SalesPersonID]
    INNER JOIN [Sales].[SalesTerritory] st 
    ON sp.[TerritoryID] = st.[TerritoryID] 
    INNER JOIN [HumanResources].[Employee] e 
    ON soh.[SalesPersonID] = e.[EmployeeID] 
    INNER JOIN [Person].[Contact] c 
    ON e.[ContactID] = c.ContactID
WHERE st.[Group] = @p1', N'@p1 nvarchar(80)', N'Europe' 





