Escenario para forzar planes: crear una guía de plan que utiliza una sugerencia de consulta USE PLAN

Artículo
12/15/2008

Se puede forzar una sugerencia de consulta al crear una guía de plan mediante el procedimiento almacenado del sistema sp_create_plan_guide. Las guías de plan se utilizan para aplicar sugerencias de consulta a las consultas en las aplicaciones implementadas cuando no se puede o no se desea cambiar la aplicación directamente. Para obtener más información acerca de las guías de plan, vea Optimizar consultas en aplicaciones implementadas mediante guías de plan. En este escenario, se adjunta la sugerencia de consulta USE PLAN a la guía de plan.

Suponga que la aplicación incluye el procedimiento almacenado siguiente:

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;

Mediante la sugerencia de consulta USE PLAN, desea adjuntar un plan de consulta a este procedimiento almacenado que se basa en un valor representativo o del tipo "el peor de los casos" para el parámetro @Country y en estadísticas actualizadas acerca de los datos de la base de datos a final de mes. Sin embargo, no puede cambiar directamente el procedimiento almacenado en la aplicación porque la compró a un fabricante de software independiente. En su lugar, puede crear una guía de plan para la consulta, especificando la sugerencia de consulta USE PLAN en la guía de plan.

Al igual que en cualquier otro escenario en el que se utiliza la sugerencia de consulta USE PLAN, debe obtener un plan XML para la consulta en el procedimiento almacenado que resulte apropiado para la aplicación forzada con USE PLAN. En este caso, a final de mes puede actualizar las estadísticas como desee y, a continuación, copiar la consulta en el procedimiento almacenado, sustituyendo un valor constante representativo o del tipo "el peor de los casos" en lugar del parámetro @Country. A continuación, ejecute la consulta con SHOWPLAN_XML habilitado.

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

Otra forma de obtener un plan XML para la consulta en el procedimiento almacenado consiste en aplicar un evento de traza en la consulta mediante el Analizador de SQL Server.

Para obtener un plan de consulta con formato XML mediante el Analizador de SQL Server

Inicie un nuevo evento del Analizador de SQL Server y seleccione el evento Showplan XML (situado debajo del nodo Performance).
Escriba un comando para que se compile la consulta, como la ejecución del procedimiento almacenado por primera vez.
Seleccione el evento Showplan XML en la traza correspondiente a la consulta.
Haga clic con el botón secundario en el evento seleccionado y seleccione Extraer datos de evento. Se le pedirá que guarde el plan XML en un archivo.

Descripción

Para utilizar el plan de consulta con formato XML (obtenido con cualquiera de los métodos descritos) en la guía de plan, pegue el plan de consulta como un literal de cadena en la sugerencia de consulta USE PLAN especificada en la cláusula OPTION de sp_create_plan_guide. En el plan XML, reemplace cada comilla simple por cuatro comillas simples para que tengan los caracteres de escape necesarios, puesto que están anidadas en dos literales de cadena. A continuación se muestra la instrucción necesaria para crear la guía de plan.

Código

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>'')'

Vea también

Compartir a través de

Escenario para forzar planes: crear una guía de plan que utiliza una sugerencia de consulta USE PLAN

Para obtener un plan de consulta con formato XML mediante el Analizador de SQL Server

Descripción

Código

Vea también

Conceptos

Otros recursos

Ayuda e información

Recursos adicionales