계획 강제 적용 시나리오: USE PLAN 쿼리 힌트를 사용하는 계획 지침 만들기

아티클
12/15/2008

sp_create_plan_guide 시스템 저장 프로시저를 사용하여 계획 지침을 만들 때 쿼리 계획을 적용할 수 있습니다. 응용 프로그램을 직접 변경할 수 없거나 직접 변경하지 않으려는 경우 배포된 응용 프로그램에서 계획 지침을 사용하여 쿼리에 쿼리 힌트를 적용합니다. 계획 지침에 대한 자세한 내용은 계획 지침을 사용하여 배포된 응용 프로그램의 쿼리 최적화를 참조하십시오. 이 시나리오에서는 계획 지침에 USE PLAN 쿼리 힌트를 추가합니다.

응용 프로그램에 다음과 같은 저장 프로시저가 있다고 가정합니다.

CREATE PROCEDURE Sales.GetSalesOrderByCountry (@Country nvarchar(60))
AS
BEGIN
    SELECT h.SalesOrderID, h.OrderDate, h.Comment
    FROM Sales.SalesOrderHeader h, Sales.Customer c,
        Sales.SalesTerritory t
    WHERE h.CustomerID = c.CustomerID AND c.TerritoryID = t.TerritoryID
        AND CountryRegionCode = @Country;
END;

USE PLAN 쿼리 힌트를 사용하여 @Country 매개 변수의 대표적인 값이나 "worst-case" 값 및 데이터베이스의 데이터에 대한 최신 통계(월말)를 기반으로 하는 쿼리 계획을 이 저장 프로시저에 추가하려고 합니다. 그러나 독립 소프트웨어 공급업체에서 응용 프로그램을 구입했으므로 응용 프로그램의 저장 프로시저를 직접 변경할 수는 없습니다. 대신 쿼리의 쿼리 지침을 만들고 쿼리 지침에 USE PLAN 쿼리 힌트를 지정할 수 있습니다.

USE PLAN 쿼리 힌트를 사용하는 다른 시나리오와 마찬가지로 USE PLAN을 사용하여 적용할 수 있는 저장 프로시저의 쿼리에 대해 XML 계획을 가져와야 합니다. 이 경우 월말에 원하는 통계를 업데이트한 다음 저장 프로시저 안에 쿼리를 복사하고 @Country 매개 변수 대신 대표적인 값이나 "worst-case" 상수 값을 사용할 수 있습니다. 그런 다음 SHOWPLAN_XML을 사용하여 쿼리를 실행합니다.

SET SHOWPLAN_XML ON;
GO
SELECT h.SalesOrderID, h.OrderDate, h.Comment
FROM Sales.SalesOrderHeader h, Sales.Customer c,
    Sales.SalesTerritory t
WHERE h.CustomerID = c.CustomerID AND c.TerritoryID = t.TerritoryID
    AND CountryRegionCode = N'US';
GO
SET SHOWPLAN_XML OFF;
GO

SQL Server 프로파일러를 사용하여 쿼리에 추적 이벤트를 적용함으로써 저장 프로시저에서 쿼리에 대한 XML 계획을 가져오는 방법도 있습니다.

SQL Server 프로파일러를 사용하여 XML 서식의 쿼리 계획을 가져오려면

SQL Server 프로파일러 추적 이벤트를 시작하고 성능 노드 아래의 실행 계획 XML을 선택합니다.
쿼리를 컴파일하는 명령(예: 처음으로 저장 프로시저 실행)을 실행합니다.
쿼리에 해당하는 추적에서 실행 계획 XML 이벤트를 선택합니다.
선택한 이벤트를 마우스 오른쪽 단추로 클릭하고 이벤트 데이터 추출을 선택합니다. XML 계획을 파일에 저장하라는 메시지가 표시됩니다.

설명

앞에서 설명한 방법 중 하나로 가져온 XML 서식의 쿼리 계획을 계획 지침에 사용하려면 sp_create_plan_guide의 OPTION 절에 지정된 USE PLAN 쿼리 힌트 안에 쿼리 계획을 문자열 리터럴로 붙여넣으십시오. 작은따옴표는 두 개의 문자열 리터럴 안에 중첩되므로 XML 계획 자체에서 작은따옴표를 각각 네 개의 작은따옴표로 바꾸어 작은따옴표를 올바르게 이스케이프합니다. 계획 지침을 만드는 데 필요한 문이 뒤에 나옵니다.

코드

EXEC sp_create_plan_guide N'Guide1',
    N'SELECT h.SalesOrderID, h.OrderDate, h.Comment
    FROM Sales.SalesOrderHeader h, Sales.Customer c,
        Sales.SalesTerritory t
    WHERE h.CustomerID = c.CustomerID AND c.TerritoryID = t.TerritoryID
        AND CountryRegionCode = @Country',
    N'OBJECT',
    N'Sales.GetSalesOrderByCountry',
    NULL,
    N'OPTION (USE PLAN
N''<ShowPlanXML xmlns="https://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.0" Build="9.00.1275">
  <BatchSequence>
    <Batch>
      <Statements>
        <StmtSimple StatementText="SELECT h.SalesOrderID, h.OrderDate, h.Comment&#xD;&#xA;FROM Sales.SalesOrderHeader h, Sales.Customer c,&#xD;&#xA;    Sales.SalesTerritory t&#xD;&#xA;WHERE h.CustomerID = c.CustomerID AND c.TerritoryID = t.TerritoryID&#xD;&#xA;    AND CountryRegionCode = N''''US''''&#xD;&#xA;" StatementId="1" StatementCompId="1" StatementType="SELECT" StatementSubTreeCost="0.897567" StatementEstRows="15942.8" 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="30">
            <RelOp NodeId="0" PhysicalOp="Hash Match" LogicalOp="Inner Join" EstimateRows="15942.8" EstimateIO="0" EstimateCPU="0.267441" AvgRowSize="151" EstimatedTotalSubtreeCost="0.897567" Parallel="0" EstimateRebinds="0" EstimateRewinds="0">
              <OutputList>
                <ColumnReference Database="[AdventureWorks]" Schema="[Sales]" Table="[SalesOrderHeader]" Alias="[h]" Column="SalesOrderID" />
                <ColumnReference Database="[AdventureWorks]" Schema="[Sales]" Table="[SalesOrderHeader]" Alias="[h]" Column="OrderDate" />
                <ColumnReference Database="[AdventureWorks]" Schema="[Sales]" Table="[SalesOrderHeader]" Alias="[h]" Column="Comment" />
              </OutputList>
              <MemoryFractions Input="1" Output="1" />
              <Hash>
                <DefinedValues />
                <HashKeysBuild>
                  <ColumnReference Database="[AdventureWorks]" Schema="[Sales]" Table="[Customer]" Alias="[c]" Column="CustomerID" />
                </HashKeysBuild>
                <HashKeysProbe>
                  <ColumnReference Database="[AdventureWorks]" Schema="[Sales]" Table="[SalesOrderHeader]" Alias="[h]" Column="CustomerID" />
                </HashKeysProbe>
                <RelOp NodeId="1" PhysicalOp="Nested Loops" LogicalOp="Inner Join" EstimateRows="9592.5" EstimateIO="0" EstimateCPU="0.0400967" AvgRowSize="11" EstimatedTotalSubtreeCost="0.0751921" Parallel="0" EstimateRebinds="0" EstimateRewinds="0">
                  <OutputList>
                    <ColumnReference Database="[AdventureWorks]" Schema="[Sales]" Table="[Customer]" Alias="[c]" Column="CustomerID" />
                  </OutputList>
                  <NestedLoops Optimized="0">
                    <OuterReferences>
                      <ColumnReference Database="[AdventureWorks]" Schema="[Sales]" Table="[SalesTerritory]" Alias="[t]" Column="TerritoryID" />
                    </OuterReferences>
                    <RelOp NodeId="2" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="5" EstimateIO="0.003125" EstimateCPU="0.000168" AvgRowSize="19" EstimatedTotalSubtreeCost="0.003293" Parallel="0" EstimateRebinds="0" EstimateRewinds="0">
                      <OutputList>
                        <ColumnReference Database="[AdventureWorks]" Schema="[Sales]" Table="[SalesTerritory]" Alias="[t]" Column="TerritoryID" />
                      </OutputList>
                      <IndexScan Ordered="0" ForcedIndex="0" NoExpandHint="0">
                        <DefinedValues>
                          <DefinedValue>
                            <ColumnReference Database="[AdventureWorks]" Schema="[Sales]" Table="[SalesTerritory]" Alias="[t]" Column="TerritoryID" />
                          </DefinedValue>
                        </DefinedValues>
                        <Object Database="[AdventureWorks]" Schema="[Sales]" Table="[SalesTerritory]" Index="[PK_SalesTerritory_TerritoryID]" Alias="[t]" />
                        <Predicate>
                          <ScalarOperator ScalarString="[AdventureWorks].[Sales].[SalesTerritory].[CountryRegionCode] as [t].[CountryRegionCode]=N''''US''''">
                            <Compare CompareOp="EQ">
                              <ScalarOperator>
                                <Identifier>
                                  <ColumnReference Database="[AdventureWorks]" Schema="[Sales]" Table="[SalesTerritory]" Alias="[t]" Column="CountryRegionCode" />
                                </Identifier>
                              </ScalarOperator>
                              <ScalarOperator>
                                <Const ConstValue="N''''US''''" />
                              </ScalarOperator>
                            </Compare>
                          </ScalarOperator>
                        </Predicate>
                      </IndexScan>
                    </RelOp>
                    <RelOp NodeId="3" PhysicalOp="Index Seek" LogicalOp="Index Seek" EstimateRows="1918.5" EstimateIO="0.00534722" EstimateCPU="0.00226735" AvgRowSize="11" EstimatedTotalSubtreeCost="0.0318004" Parallel="0" EstimateRebinds="4" EstimateRewinds="0">
                      <OutputList>
                        <ColumnReference Database="[AdventureWorks]" Schema="[Sales]" Table="[Customer]" Alias="[c]" Column="CustomerID" />
                      </OutputList>
                      <IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" NoExpandHint="0">
                        <DefinedValues>
                          <DefinedValue>
                            <ColumnReference Database="[AdventureWorks]" Schema="[Sales]" Table="[Customer]" Alias="[c]" Column="CustomerID" />
                          </DefinedValue>
                        </DefinedValues>
                        <Object Database="[AdventureWorks]" Schema="[Sales]" Table="[Customer]" Index="[IX_Customer_TerritoryID]" Alias="[c]" />
                        <SeekPredicates>
                          <SeekPredicate>
                            <Prefix ScanType="EQ">
                              <RangeColumns>
                                <ColumnReference Database="[AdventureWorks]" Schema="[Sales]" Table="[Customer]" Alias="[c]" Column="TerritoryID" />
                              </RangeColumns>
                              <RangeExpressions>
                                <ScalarOperator ScalarString="[AdventureWorks].[Sales].[SalesTerritory].[TerritoryID] as [t].[TerritoryID]">
                                  <Identifier>
                                    <ColumnReference Database="[AdventureWorks]" Schema="[Sales]" Table="[SalesTerritory]" Alias="[t]" Column="TerritoryID" />
                                  </Identifier>
                                </ScalarOperator>
                              </RangeExpressions>
                            </Prefix>
                          </SeekPredicate>
                        </SeekPredicates>
                      </IndexScan>
                    </RelOp>
                  </NestedLoops>
                </RelOp>
                <RelOp NodeId="4" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="31465" EstimateIO="0.520162" EstimateCPU="0.0347685" AvgRowSize="155" EstimatedTotalSubtreeCost="0.554931" Parallel="0" EstimateRebinds="0" EstimateRewinds="0">
                  <OutputList>
                    <ColumnReference Database="[AdventureWorks]" Schema="[Sales]" Table="[SalesOrderHeader]" Alias="[h]" Column="SalesOrderID" />
                    <ColumnReference Database="[AdventureWorks]" Schema="[Sales]" Table="[SalesOrderHeader]" Alias="[h]" Column="OrderDate" />
                    <ColumnReference Database="[AdventureWorks]" Schema="[Sales]" Table="[SalesOrderHeader]" Alias="[h]" Column="CustomerID" />
                    <ColumnReference Database="[AdventureWorks]" Schema="[Sales]" Table="[SalesOrderHeader]" Alias="[h]" Column="Comment" />
                  </OutputList>
                  <IndexScan Ordered="0" ForcedIndex="0" NoExpandHint="0">
                    <DefinedValues>
                      <DefinedValue>
                        <ColumnReference Database="[AdventureWorks]" Schema="[Sales]" Table="[SalesOrderHeader]" Alias="[h]" Column="SalesOrderID" />
                      </DefinedValue>
                      <DefinedValue>
                        <ColumnReference Database="[AdventureWorks]" Schema="[Sales]" Table="[SalesOrderHeader]" Alias="[h]" Column="OrderDate" />
                      </DefinedValue>
                      <DefinedValue>
                        <ColumnReference Database="[AdventureWorks]" Schema="[Sales]" Table="[SalesOrderHeader]" Alias="[h]" Column="CustomerID" />
                      </DefinedValue>
                      <DefinedValue>
                        <ColumnReference Database="[AdventureWorks]" Schema="[Sales]" Table="[SalesOrderHeader]" Alias="[h]" Column="Comment" />
                      </DefinedValue>
                    </DefinedValues>
                    <Object Database="[AdventureWorks]" Schema="[Sales]" Table="[SalesOrderHeader]" Index="[PK_SalesOrderHeader_SalesOrderID]" Alias="[h]" />
                  </IndexScan>
                </RelOp>
              </Hash>
            </RelOp>
          </QueryPlan>
        </StmtSimple>
      </Statements>
    </Batch>
  </BatchSequence>
</ShowPlanXML>'')'

참고 항목

개념

계획 강제 적용 시나리오 및 예
계획 강제 적용을 사용하여 쿼리 계획 지정

도움말 및 정보

SQL Server 2005 지원 받기

다음을 통해 공유

계획 강제 적용 시나리오: USE PLAN 쿼리 힌트를 사용하는 계획 지침 만들기

SQL Server 프로파일러를 사용하여 XML 서식의 쿼리 계획을 가져오려면

설명

코드

참고 항목

개념

관련 자료

도움말 및 정보

추가 리소스