Поделиться через

Сценарий форсирования планов: создание руководства плана, использующего в запросе подсказку 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, нужно получить 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.

Получение плана запроса в формате XML с помощью приложения SQL Server Profiler

  1. Создайте новое событие трассировки в приложении SQL Server Profiler и выберите событие Showplan XML (оно находится в узле Производительность).

  2. Выполните команду компиляции запроса, например, запустите в первый раз хранимую процедуру.

  3. Выберите в трассировке событие Showplan XML, соответствующее запросу.

  4. Щелкните правой кнопкой мыши выбранное событие и выберите Извлечь данные события. Будет предложено сохранить XML-план в файл.

Описание

Чтобы использовать план запроса в формате XML (полученный любым их описанных выше способов) в руководстве плана, вставьте его как строковый литерал в подсказку USE PLAN предложения OPTION процедуры sp_create_plan_guide. В самом 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