共用方式為

計劃強制實例：建立使用 USE PLAN 查詢提示的計劃指南

  • 發行項

當您使用 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 參數的代表值或最糟榚的情況值，以及資料庫中月底資料的最新統計資料而來。然而，您無法直接變更應用程式中的預存程序，因為您是從獨立的軟體廠商購買該應用程式。替代的作法是您可以建立查詢的計劃指南，並在計劃指南中指定 USE PLAN 查詢提示。

就如同任何其他的狀況，當您使用 USE PLAN 查詢提示時，您必須在適合強制執行 USE PLAN 的預存程序中取得查詢的 XML 計劃。如此一來，在月底時，您就可以更新您所需的統計資料，然後在預存程序中複製查詢，以代表常值或最糟榚的情況常值來取代 @Country 常數。接著，執行啟用 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

另一個方法可取得預存程序中查詢的 XML 計劃，是使用 SQL Server Profiler 在查詢中套用追蹤事件。

若要使用 SQL Server Profiler 取得 XML 格式的查詢計劃

  1. 啟動新的 SQL Server Profiler 追蹤事件並選取 Showplan XML 事件 (位於 [效能] 節點之下)。

  2. 發出命令以編譯查詢，例如，第一次執行預存程序。

  3. 在與查詢對應的追蹤中選取 Showplan XML 事件。

  4. 以滑鼠右鍵按一下選取的事件並選取 [擷取事件資料]。將會提示您將 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>'')'

請參閱

概念

強制執行計劃的狀況和範例
以強制執行計劃來指定查詢計劃

其他資源

查詢效能
sp_create_plan_guide (Transact-SQL)

說明及資訊

取得 SQL Server 2005 協助