/**********************************************************************************************
-- Title : [2k5] 실행 계획 지침에서 USE_PLAN 데모
-- Reference : mcpworld.com
-- Key word : sp_create_plan_guide, use_plan
**********************************************************************************************/
USE AdventureWorks
GO
--STEP1) USE PLAN 옵션을 설정하기 전의 실행 계획
--(Nested Loops 임)
SET SHOWPLAN_ALL ON
GO
SELECT A.EmployeeID, A.ContactID, A.LoginID, B.NameStyle, B.EmailAddress
FROM HumanResources.Employee A JOIN Person.Contact B ON A.ContactID = B.ContactID
GO
/*
SELECT A.EmployeeID, A.ContactID, A.LoginID, B.NameStyle, B.EmailAddress FROM HumanResources.Employee A JOIN Person.Contact B ON A.ContactID = B.ContactID
|--Nested Loops(Inner Join, OUTER REFERENCES:([A].[ContactID], [Expr1004]) WITH UNORDERED PREFETCH)
|--Clustered Index Scan(OBJECT:([AdventureWorks].[HumanResources].[Employee].[PK_Employee_EmployeeID] AS [A]))
|--Clustered Index Seek(OBJECT:([AdventureWorks].[Person].[Contact].[PK_Contact_ContactID] AS [B]), SEEK:([B].[ContactID]=[AdventureWorks].[HumanResources].[Employee].[ContactID] as [A].[ContactID]) ORDERED FORWARD)
*/
SET SHOWPLAN_ALL OFF
GO
--STEP2) 해당 쿼리의 실행 계획을 XML 형태로 생성
-- Nested Loop 대신 Merge Join 을 수행하도록 하기 위해 Option (Merge Join) 을 추가하여 실행 계획을 생성함.
SET SHOWPLAN_XML ON
GO
SELECT A.EmployeeID, A.ContactID, A.LoginID, B.NameStyle, B.EmailAddress
FROM HumanResources.Employee A JOIN Person.Contact B ON A.ContactID = B.ContactID
OPTION (MERGE JOIN)
GO
SET SHOWPLAN_XML OFF
GO
--생성한 실행 계획
/*
<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.0" Build="9.00.3159.00">
<BatchSequence>
<Batch>
<Statements>
<StmtSimple StatementText="SELECT A.EmployeeID, A.ContactID, A.LoginID, B.NameStyle, B.EmailAddress FROM HumanResources.Employee A JOIN Person.Contact B ON A.ContactID = B.ContactID OPTION (MERGE JOIN) " StatementId="1" StatementCompId="1" StatementType="SELECT" StatementSubTreeCost="0.509874" StatementEstRows="290" 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 CachedPlanSize="11" CompileTime="2" CompileCPU="2" CompileMemory="104">
<RelOp NodeId="0" PhysicalOp="Merge Join" LogicalOp="Inner Join" EstimateRows="290" EstimateIO="0" EstimateCPU="0.0481792" AvgRowSize="328" EstimatedTotalSubtreeCost="0.509874" Parallel="0" EstimateRebinds="0" EstimateRewinds="0">
<OutputList>
<ColumnReference Database="[AdventureWorks]" Schema="[HumanResources]" Table="[Employee]" Alias="[A]" Column="EmployeeID" />
<ColumnReference Database="[AdventureWorks]" Schema="[HumanResources]" Table="[Employee]" Alias="[A]" Column="ContactID" />
<ColumnReference Database="[AdventureWorks]" Schema="[HumanResources]" Table="[Employee]" Alias="[A]" Column="LoginID" />
<ColumnReference Database="[AdventureWorks]" Schema="[Person]" Table="[Contact]" Alias="[B]" Column="NameStyle" />
<ColumnReference Database="[AdventureWorks]" Schema="[Person]" Table="[Contact]" Alias="[B]" Column="EmailAddress" />
</OutputList>
<Merge ManyToMany="0">
<InnerSideJoinColumns>
<ColumnReference Database="[AdventureWorks]" Schema="[HumanResources]" Table="[Employee]" Alias="[A]" Column="ContactID" />
</InnerSideJoinColumns>
<OuterSideJoinColumns>
<ColumnReference Database="[AdventureWorks]" Schema="[Person]" Table="[Contact]" Alias="[B]" Column="ContactID" />
</OuterSideJoinColumns>
<Residual>
<ScalarOperator ScalarString="[AdventureWorks].[Person].[Contact].[ContactID] as [B].[ContactID]=[AdventureWorks].[HumanResources].[Employee].[ContactID] as [A].[ContactID]">
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[AdventureWorks]" Schema="[Person]" Table="[Contact]" Alias="[B]" Column="ContactID" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[AdventureWorks]" Schema="[HumanResources]" Table="[Employee]" Alias="[A]" Column="ContactID" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</Residual>
<RelOp NodeId="1" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="19972" EstimateIO="0.416458" EstimateCPU="0.0221262" AvgRowSize="66" EstimatedTotalSubtreeCost="0.438585" Parallel="0" EstimateRebinds="0" EstimateRewinds="0">
<OutputList>
<ColumnReference Database="[AdventureWorks]" Schema="[Person]" Table="[Contact]" Alias="[B]" Column="ContactID" />
<ColumnReference Database="[AdventureWorks]" Schema="[Person]" Table="[Contact]" Alias="[B]" Column="NameStyle" />
<ColumnReference Database="[AdventureWorks]" Schema="[Person]" Table="[Contact]" Alias="[B]" Column="EmailAddress" />
</OutputList>
<IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" NoExpandHint="0">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[AdventureWorks]" Schema="[Person]" Table="[Contact]" Alias="[B]" Column="ContactID" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[AdventureWorks]" Schema="[Person]" Table="[Contact]" Alias="[B]" Column="NameStyle" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[AdventureWorks]" Schema="[Person]" Table="[Contact]" Alias="[B]" Column="EmailAddress" />
</DefinedValue>
</DefinedValues>
<Object Database="[AdventureWorks]" Schema="[Person]" Table="[Contact]" Index="[PK_Contact_ContactID]" Alias="[B]" />
</IndexScan>
</RelOp>
<RelOp NodeId="2" PhysicalOp="Sort" LogicalOp="Sort" EstimateRows="290" EstimateIO="0.0112613" EstimateCPU="0.00380087" AvgRowSize="275" EstimatedTotalSubtreeCost="0.0231076" Parallel="0" EstimateRebinds="0" EstimateRewinds="0">
<OutputList>
<ColumnReference Database="[AdventureWorks]" Schema="[HumanResources]" Table="[Employee]" Alias="[A]" Column="EmployeeID" />
<ColumnReference Database="[AdventureWorks]" Schema="[HumanResources]" Table="[Employee]" Alias="[A]" Column="ContactID" />
<ColumnReference Database="[AdventureWorks]" Schema="[HumanResources]" Table="[Employee]" Alias="[A]" Column="LoginID" />
</OutputList>
<MemoryFractions Input="1" Output="1" />
<Sort Distinct="0">
<OrderBy>
<OrderByColumn Ascending="1">
<ColumnReference Database="[AdventureWorks]" Schema="[HumanResources]" Table="[Employee]" Alias="[A]" Column="ContactID" />
</OrderByColumn>
</OrderBy>
<RelOp NodeId="3" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="290" EstimateIO="0.00756944" EstimateCPU="0.000476" AvgRowSize="275" EstimatedTotalSubtreeCost="0.00804544" Parallel="0" EstimateRebinds="0" EstimateRewinds="0">
<OutputList>
<ColumnReference Database="[AdventureWorks]" Schema="[HumanResources]" Table="[Employee]" Alias="[A]" Column="EmployeeID" />
<ColumnReference Database="[AdventureWorks]" Schema="[HumanResources]" Table="[Employee]" Alias="[A]" Column="ContactID" />
<ColumnReference Database="[AdventureWorks]" Schema="[HumanResources]" Table="[Employee]" Alias="[A]" Column="LoginID" />
</OutputList>
<IndexScan Ordered="0" ForcedIndex="0" NoExpandHint="0">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[AdventureWorks]" Schema="[HumanResources]" Table="[Employee]" Alias="[A]" Column="EmployeeID" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[AdventureWorks]" Schema="[HumanResources]" Table="[Employee]" Alias="[A]" Column="ContactID" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[AdventureWorks]" Schema="[HumanResources]" Table="[Employee]" Alias="[A]" Column="LoginID" />
</DefinedValue>
</DefinedValues>
<Object Database="[AdventureWorks]" Schema="[HumanResources]" Table="[Employee]" Index="[PK_Employee_EmployeeID]" Alias="[A]" />
</IndexScan>
</RelOp>
</Sort>
</RelOp>
</Merge>
</RelOp>
</QueryPlan>
</StmtSimple>
</Statements>
</Batch>
</BatchSequence>
</ShowPlanXML>
*/
--STEP3) SP_CONTROL_PLAN_GUIDE 를 이용하여 해당 쿼리에 대해 강제 실행 계획을 설정
IF EXISTS (SELECT * FROM SYS.PLAN_GUIDES WHERE NAME = N'PlanGuide1')
EXEC sp_control_plan_guide N'DROP',N'PlanGuide1'
GO
EXEC sp_create_plan_guide
@name = N'PlanGuide1',
@stmt = N'SELECT A.EmployeeID, A.ContactID, A.LoginID, B.NameStyle, B.EmailAddress
FROM HumanResources.Employee A JOIN Person.Contact B ON A.ContactID = B.ContactID',
@type = N'SQL',
@module_or_batch = NULL,
@params = NULL,
@hints = N'OPTION (USE PLAN ''<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.0" Build="9.00.3159.00">
<BatchSequence>
<Batch>
<Statements>
<StmtSimple StatementText="SELECT A.EmployeeID, A.ContactID, A.LoginID, B.NameStyle, B.EmailAddress FROM HumanResources.Employee A JOIN Person.Contact B ON A.ContactID = B.ContactID OPTION (MERGE JOIN) " StatementId="1" StatementCompId="1" StatementType="SELECT" StatementSubTreeCost="0.509874" StatementEstRows="290" 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 CachedPlanSize="11" CompileTime="2" CompileCPU="2" CompileMemory="104">
<RelOp NodeId="0" PhysicalOp="Merge Join" LogicalOp="Inner Join" EstimateRows="290" EstimateIO="0" EstimateCPU="0.0481792" AvgRowSize="328" EstimatedTotalSubtreeCost="0.509874" Parallel="0" EstimateRebinds="0" EstimateRewinds="0">
<OutputList>
<ColumnReference Database="[AdventureWorks]" Schema="[HumanResources]" Table="[Employee]" Alias="[A]" Column="EmployeeID" />
<ColumnReference Database="[AdventureWorks]" Schema="[HumanResources]" Table="[Employee]" Alias="[A]" Column="ContactID" />
<ColumnReference Database="[AdventureWorks]" Schema="[HumanResources]" Table="[Employee]" Alias="[A]" Column="LoginID" />
<ColumnReference Database="[AdventureWorks]" Schema="[Person]" Table="[Contact]" Alias="[B]" Column="NameStyle" />
<ColumnReference Database="[AdventureWorks]" Schema="[Person]" Table="[Contact]" Alias="[B]" Column="EmailAddress" />
</OutputList>
<Merge ManyToMany="0">
<InnerSideJoinColumns>
<ColumnReference Database="[AdventureWorks]" Schema="[HumanResources]" Table="[Employee]" Alias="[A]" Column="ContactID" />
</InnerSideJoinColumns>
<OuterSideJoinColumns>
<ColumnReference Database="[AdventureWorks]" Schema="[Person]" Table="[Contact]" Alias="[B]" Column="ContactID" />
</OuterSideJoinColumns>
<Residual>
<ScalarOperator ScalarString="[AdventureWorks].[Person].[Contact].[ContactID] as [B].[ContactID]=[AdventureWorks].[HumanResources].[Employee].[ContactID] as [A].[ContactID]">
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[AdventureWorks]" Schema="[Person]" Table="[Contact]" Alias="[B]" Column="ContactID" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[AdventureWorks]" Schema="[HumanResources]" Table="[Employee]" Alias="[A]" Column="ContactID" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</Residual>
<RelOp NodeId="1" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="19972" EstimateIO="0.416458" EstimateCPU="0.0221262" AvgRowSize="66" EstimatedTotalSubtreeCost="0.438585" Parallel="0" EstimateRebinds="0" EstimateRewinds="0">
<OutputList>
<ColumnReference Database="[AdventureWorks]" Schema="[Person]" Table="[Contact]" Alias="[B]" Column="ContactID" />
<ColumnReference Database="[AdventureWorks]" Schema="[Person]" Table="[Contact]" Alias="[B]" Column="NameStyle" />
<ColumnReference Database="[AdventureWorks]" Schema="[Person]" Table="[Contact]" Alias="[B]" Column="EmailAddress" />
</OutputList>
<IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" NoExpandHint="0">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[AdventureWorks]" Schema="[Person]" Table="[Contact]" Alias="[B]" Column="ContactID" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[AdventureWorks]" Schema="[Person]" Table="[Contact]" Alias="[B]" Column="NameStyle" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[AdventureWorks]" Schema="[Person]" Table="[Contact]" Alias="[B]" Column="EmailAddress" />
</DefinedValue>
</DefinedValues>
<Object Database="[AdventureWorks]" Schema="[Person]" Table="[Contact]" Index="[PK_Contact_ContactID]" Alias="[B]" />
</IndexScan>
</RelOp>
<RelOp NodeId="2" PhysicalOp="Sort" LogicalOp="Sort" EstimateRows="290" EstimateIO="0.0112613" EstimateCPU="0.00380087" AvgRowSize="275" EstimatedTotalSubtreeCost="0.0231076" Parallel="0" EstimateRebinds="0" EstimateRewinds="0">
<OutputList>
<ColumnReference Database="[AdventureWorks]" Schema="[HumanResources]" Table="[Employee]" Alias="[A]" Column="EmployeeID" />
<ColumnReference Database="[AdventureWorks]" Schema="[HumanResources]" Table="[Employee]" Alias="[A]" Column="ContactID" />
<ColumnReference Database="[AdventureWorks]" Schema="[HumanResources]" Table="[Employee]" Alias="[A]" Column="LoginID" />
</OutputList>
<MemoryFractions Input="1" Output="1" />
<Sort Distinct="0">
<OrderBy>
<OrderByColumn Ascending="1">
<ColumnReference Database="[AdventureWorks]" Schema="[HumanResources]" Table="[Employee]" Alias="[A]" Column="ContactID" />
</OrderByColumn>
</OrderBy>
<RelOp NodeId="3" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="290" EstimateIO="0.00756944" EstimateCPU="0.000476" AvgRowSize="275" EstimatedTotalSubtreeCost="0.00804544" Parallel="0" EstimateRebinds="0" EstimateRewinds="0">
<OutputList>
<ColumnReference Database="[AdventureWorks]" Schema="[HumanResources]" Table="[Employee]" Alias="[A]" Column="EmployeeID" />
<ColumnReference Database="[AdventureWorks]" Schema="[HumanResources]" Table="[Employee]" Alias="[A]" Column="ContactID" />
<ColumnReference Database="[AdventureWorks]" Schema="[HumanResources]" Table="[Employee]" Alias="[A]" Column="LoginID" />
</OutputList>
<IndexScan Ordered="0" ForcedIndex="0" NoExpandHint="0">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[AdventureWorks]" Schema="[HumanResources]" Table="[Employee]" Alias="[A]" Column="EmployeeID" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[AdventureWorks]" Schema="[HumanResources]" Table="[Employee]" Alias="[A]" Column="ContactID" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[AdventureWorks]" Schema="[HumanResources]" Table="[Employee]" Alias="[A]" Column="LoginID" />
</DefinedValue>
</DefinedValues>
<Object Database="[AdventureWorks]" Schema="[HumanResources]" Table="[Employee]" Index="[PK_Employee_EmployeeID]" Alias="[A]" />
</IndexScan>
</RelOp>
</Sort>
</RelOp>
</Merge>
</RelOp>
</QueryPlan>
</StmtSimple>
</Statements>
</Batch>
</BatchSequence>
</ShowPlanXML>'')'
GO
/*추가된 PLAN GUIDE 확인 */
SELECT * FROM sys.plan_guides
GO
--STEP4) 쿼리에 대해 실행 확인
--Nested Loop 대신 Merge Join 으로 변경된 것을 확인
SET SHOWPLAN_ALL ON
GO
--주의) 쿼리를 정확히 선택해서 실행해야 함 : SELECT 의 S 부터 GO 의 O 까지 정확히 선택한 후 실행
SELECT A.EmployeeID, A.ContactID, A.LoginID, B.NameStyle, B.EmailAddress
FROM HumanResources.Employee A JOIN Person.Contact B ON A.ContactID = B.ContactID
GO
/*
SELECT A.EmployeeID, A.ContactID, A.LoginID, B.NameStyle, B.EmailAddress FROM HumanResources.Employee A JOIN Person.Contact B ON A.ContactID = B.ContactID
|--Merge Join(Inner Join, MERGE:([B].[ContactID])=([A].[ContactID]), RESIDUAL:([AdventureWorks].[Person].[Contact].[ContactID] as [B].[ContactID]=[AdventureWorks].[HumanResources].[Employee].[ContactID] as [A].[ContactID]))
|--Clustered Index Scan(OBJECT:([AdventureWorks].[Person].[Contact].[PK_Contact_ContactID] AS [B]), ORDERED FORWARD)
|--Sort(ORDER BY:([A].[ContactID] ASC))
|--Clustered Index Scan(OBJECT:([AdventureWorks].[HumanResources].[Employee].[PK_Employee_EmployeeID] AS [A]))
*/
SET SHOWPLAN_ALL OFF
GO
--STEP5) CLEAN UP
IF EXISTS (SELECT * FROM SYS.PLAN_GUIDES WHERE NAME = N'PlanGuide1')
EXEC sp_control_plan_guide N'DROP',N'PlanGuide1'
GO