Partitioned-View не работает с параметрами

Question

Partitioned-View не работает с параметрами

В настоящее время у нас есть несколько клиентов, где их запросам потребовались бы годы, чтобы получить результаты из секционированного представления. При ближайшем рассмотрении мы нашли план выполнения для сканирования всех таблиц, а не только таблиц с соответствующими данными.

Чтобы изолировать это поведение, я взял пример многораздельного представления из https://technet.microsoft.com/en-us/library/ms190019(v=sql.105).aspx и заново создал нашу проблему:

В этом примере у нас есть 12 таблиц (для каждого месяца 1998 года):

CREATE TABLE Jan1998sales
(
    OrderID INT,
    CustomerID INT NOT NULL,
    OrderDate DATETIME NULL CHECK(DATEPART(yy, OrderDate) = 1998),
    OrderMonth INT CHECK (OrderMonth = 1),
    DeliveryDate DATETIME NULL CHECK(DATEPART(MM, DeliveryDate) = 1),
    CONSTRAINT Jan1998sales_OrderIDMonth PRIMARY KEY (OrderID, OrderMonth)
)

Мы смотрим на вид, который выглядит следующим образом:

CREATE VIEW Year1998Sales
AS
(
    SELECT * FROM Jan1998Sales
    UNION ALL
    SELECT * FROM Feb1998Sales
    UNION ALL
    SELECT * FROM Mar1998Sales
    UNION ALL
    [...]
    UNION ALL
    SELECT * FROM Dec1998Sales
)

Теперь у нас есть один запрос, который прекрасно работает (только сканирует необходимые таблицы):

SELECT * FROM Year1998Sales
WHERE (OrderMonth = 5 OR OrderMonth = 6) AND CustomerID = 64892

Но если мы фильтруем по параметрам, он неожиданно сканирует все таблицы:

DECLARE @MonthA int = 5
DECLARE @MonthB int = 6

SELECT * FROM Year1998Sales
WHERE (OrderMonth = @MonthA OR OrderMonth = @MonthB) AND CustomerID = 64892

Мое первое предположение, объясняющее такое поведение, заключается в том, что Microsoft SQL Server создает план выполнения один раз, который сканирует все таблицы и использует его для каждого выполнения этого запроса, таким образом, сканируя все таблицы все время.

Кто-нибудь знает, как мы могли заставить SQL Server сканировать только необходимые таблицы и при этом использовать параметризованный фильтр? Или кто-нибудь может подтвердить, что это ошибка в построителе плана выполнения SQL Server?

Для полного кода взгляните на эту скрипту SQL: http://sqlfiddle.com/

4

sql-server partitioned-view

Источник

user4871566 16 авг '17 в 11:32

1 ответ

Решение

Другие вопросы по тегам sql-server partitioned-view

user3711162 16 авг '17 в 12:27 2017-08-16 12:27 · Accepted Answer · 2017-08-16 12:27

Вообще говоря, OR Предикаты являются сложной задачей для SQL Server по оптимизации и созданию кешируемого плана многократного использования.

Я выполнил запрос с OPTION(RECOMPILE) подсказка запроса и фактический план выполнения показывает, что ненужные таблицы элементов многораздельного представления статически исключаются из плана. Не знаю, почему sqlfiddle не показывает это (в настоящее время используется RTM-версия SQL 2014), но я наблюдал исключение всех версий SQL Server RC2 с 2012 по 2017 год с установленными последними пакетами обновлений.

DECLARE @MonthA int = 5
DECLARE @MonthB int = 6;

SELECT *
FROM Year1998Sales
WHERE (OrderMonth = @MonthA OR OrderMonth = @MonthB) AND CustomerID = 64892
OPTION(RECOMPILE);

Вот фактический план выполнения XML (SQL Server 2017 RC2):

<?xml version="1.0" encoding="utf-16"?>
<ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.6" Build="14.0.900.75" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
  <BatchSequence>
    <Batch>
      <Statements>
        <StmtSimple StatementCompId="3" StatementEstRows="2" StatementId="1" StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="140" StatementSubTreeCost="0.00656736" StatementText="SELECT *&#xD;&#xA;FROM Year1998Sales&#xD;&#xA;WHERE (OrderMonth = @MonthA OR OrderMonth = @MonthB) AND CustomerID = 64892&#xD;&#xA;OPTION(RECOMPILE)" StatementType="SELECT" QueryHash="0xF9DB04D00D56A43D" QueryPlanHash="0x6171395FA7A2F92C" RetrievedFromCache="false" SecurityPolicyApplied="false">
          <StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" />
          <QueryPlan DegreeOfParallelism="1" CachedPlanSize="24" CompileTime="8" CompileCPU="8" CompileMemory="552">
            <MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" />
            <OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="419405" EstimatedPagesCached="104851" EstimatedAvailableDegreeOfParallelism="2" MaxCompileMemory="9985376" />
            <QueryTimeStats CpuTime="0" ElapsedTime="0" />
            <RelOp AvgRowSize="35" EstimateCPU="2E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="2" LogicalOp="Concatenation" NodeId="0" Parallel="false" PhysicalOp="Concatenation" EstimatedTotalSubtreeCost="0.00656736">
              <OutputList>
                <ColumnReference Column="Union1014" />
                <ColumnReference Column="Union1015" />
                <ColumnReference Column="Union1016" />
                <ColumnReference Column="Union1017" />
                <ColumnReference Column="Union1018" />
              </OutputList>
              <RunTimeInformation>
                <RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" />
              </RunTimeInformation>
              <Concat>
                <DefinedValues>
                  <DefinedValue>
                    <ColumnReference Column="Union1014" />
                    <ColumnReference Database="[Repro]" Schema="[dbo]" Table="[May1998sales]" Column="OrderID" />
                    <ColumnReference Database="[Repro]" Schema="[dbo]" Table="[Jun1998sales]" Column="OrderID" />
                  </DefinedValue>
                  <DefinedValue>
                    <ColumnReference Column="Union1015" />
                    <ColumnReference Database="[Repro]" Schema="[dbo]" Table="[May1998sales]" Column="CustomerID" />
                    <ColumnReference Database="[Repro]" Schema="[dbo]" Table="[Jun1998sales]" Column="CustomerID" />
                  </DefinedValue>
                  <DefinedValue>
                    <ColumnReference Column="Union1016" />
                    <ColumnReference Database="[Repro]" Schema="[dbo]" Table="[May1998sales]" Column="OrderDate" />
                    <ColumnReference Database="[Repro]" Schema="[dbo]" Table="[Jun1998sales]" Column="OrderDate" />
                  </DefinedValue>
                  <DefinedValue>
                    <ColumnReference Column="Union1017" />
                    <ColumnReference Database="[Repro]" Schema="[dbo]" Table="[May1998sales]" Column="OrderMonth" />
                    <ColumnReference Database="[Repro]" Schema="[dbo]" Table="[Jun1998sales]" Column="OrderMonth" />
                  </DefinedValue>
                  <DefinedValue>
                    <ColumnReference Column="Union1018" />
                    <ColumnReference Database="[Repro]" Schema="[dbo]" Table="[May1998sales]" Column="DeliveryDate" />
                    <ColumnReference Database="[Repro]" Schema="[dbo]" Table="[Jun1998sales]" Column="DeliveryDate" />
                  </DefinedValue>
                </DefinedValues>
                <RelOp AvgRowSize="35" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" EstimatedRowsRead="1" LogicalOp="Clustered Index Scan" NodeId="1" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="0">
                  <OutputList>
                    <ColumnReference Database="[Repro]" Schema="[dbo]" Table="[May1998sales]" Column="OrderID" />
                    <ColumnReference Database="[Repro]" Schema="[dbo]" Table="[May1998sales]" Column="CustomerID" />
                    <ColumnReference Database="[Repro]" Schema="[dbo]" Table="[May1998sales]" Column="OrderDate" />
                    <ColumnReference Database="[Repro]" Schema="[dbo]" Table="[May1998sales]" Column="OrderMonth" />
                    <ColumnReference Database="[Repro]" Schema="[dbo]" Table="[May1998sales]" Column="DeliveryDate" />
                  </OutputList>
                  <RunTimeInformation>
                    <RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
                  </RunTimeInformation>
                  <IndexScan Ordered="false" ForcedIndex="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
                    <DefinedValues>
                      <DefinedValue>
                        <ColumnReference Database="[Repro]" Schema="[dbo]" Table="[May1998sales]" Column="OrderID" />
                      </DefinedValue>
                      <DefinedValue>
                        <ColumnReference Database="[Repro]" Schema="[dbo]" Table="[May1998sales]" Column="CustomerID" />
                      </DefinedValue>
                      <DefinedValue>
                        <ColumnReference Database="[Repro]" Schema="[dbo]" Table="[May1998sales]" Column="OrderDate" />
                      </DefinedValue>
                      <DefinedValue>
                        <ColumnReference Database="[Repro]" Schema="[dbo]" Table="[May1998sales]" Column="OrderMonth" />
                      </DefinedValue>
                      <DefinedValue>
                        <ColumnReference Database="[Repro]" Schema="[dbo]" Table="[May1998sales]" Column="DeliveryDate" />
                      </DefinedValue>
                    </DefinedValues>
                    <Object Database="[Repro]" Schema="[dbo]" Table="[May1998sales]" Index="[May1998sales_OrderIDMonth]" IndexKind="Clustered" Storage="RowStore" />
                    <Predicate>
                      <ScalarOperator ScalarString="[Repro].[dbo].[May1998sales].[CustomerID]=(64892)">
                        <Compare CompareOp="EQ">
                          <ScalarOperator>
                            <Identifier>
                              <ColumnReference Database="[Repro]" Schema="[dbo]" Table="[May1998sales]" Column="CustomerID" />
                            </Identifier>
                          </ScalarOperator>
                          <ScalarOperator>
                            <Const ConstValue="(64892)" />
                          </ScalarOperator>
                        </Compare>
                      </ScalarOperator>
                    </Predicate>
                  </IndexScan>
                </RelOp>
                <RelOp AvgRowSize="35" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" EstimatedRowsRead="1" LogicalOp="Clustered Index Scan" NodeId="2" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="0">
                  <OutputList>
                    <ColumnReference Database="[Repro]" Schema="[dbo]" Table="[Jun1998sales]" Column="OrderID" />
                    <ColumnReference Database="[Repro]" Schema="[dbo]" Table="[Jun1998sales]" Column="CustomerID" />
                    <ColumnReference Database="[Repro]" Schema="[dbo]" Table="[Jun1998sales]" Column="OrderDate" />
                    <ColumnReference Database="[Repro]" Schema="[dbo]" Table="[Jun1998sales]" Column="OrderMonth" />
                    <ColumnReference Database="[Repro]" Schema="[dbo]" Table="[Jun1998sales]" Column="DeliveryDate" />
                  </OutputList>
                  <RunTimeInformation>
                    <RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
                  </RunTimeInformation>
                  <IndexScan Ordered="false" ForcedIndex="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
                    <DefinedValues>
                      <DefinedValue>
                        <ColumnReference Database="[Repro]" Schema="[dbo]" Table="[Jun1998sales]" Column="OrderID" />
                      </DefinedValue>
                      <DefinedValue>
                        <ColumnReference Database="[Repro]" Schema="[dbo]" Table="[Jun1998sales]" Column="CustomerID" />
                      </DefinedValue>
                      <DefinedValue>
                        <ColumnReference Database="[Repro]" Schema="[dbo]" Table="[Jun1998sales]" Column="OrderDate" />
                      </DefinedValue>
                      <DefinedValue>
                        <ColumnReference Database="[Repro]" Schema="[dbo]" Table="[Jun1998sales]" Column="OrderMonth" />
                      </DefinedValue>
                      <DefinedValue>
                        <ColumnReference Database="[Repro]" Schema="[dbo]" Table="[Jun1998sales]" Column="DeliveryDate" />
                      </DefinedValue>
                    </DefinedValues>
                    <Object Database="[Repro]" Schema="[dbo]" Table="[Jun1998sales]" Index="[Jun1998sales_OrderIDMonth]" IndexKind="Clustered" Storage="RowStore" />
                    <Predicate>
                      <ScalarOperator ScalarString="[Repro].[dbo].[Jun1998sales].[CustomerID]=(64892)">
                        <Compare CompareOp="EQ">
                          <ScalarOperator>
                            <Identifier>
                              <ColumnReference Database="[Repro]" Schema="[dbo]" Table="[Jun1998sales]" Column="CustomerID" />
                            </Identifier>
                          </ScalarOperator>
                          <ScalarOperator>
                            <Const ConstValue="(64892)" />
                          </ScalarOperator>
                        </Compare>
                      </ScalarOperator>
                    </Predicate>
                  </IndexScan>
                </RelOp>
              </Concat>
            </RelOp>
          </QueryPlan>
        </StmtSimple>
      </Statements>
    </Batch>
  </BatchSequence>
</ShowPlanXML>