Partitioned-View not working with parameters

Question

We currenlty have a few customers where their querys would take ages to get results out of a partitioned view. On closer inspection we found the execution plan to scan every table, instead of just the ones with the relevant data.

To isolate this behaviour, I took the partitioned view example from https://technet.microsoft.com/en-us/library/ms190019(v=sql.105).aspx and recreated our problem:

In this example, we have 12 tables (for each month of the year 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)
)

We are looking at a view that looks as follows:

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

Now we have one query that works perfectly fine (only scans the necessary tables):

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

But if we filter with parameters, it suddently scans all tables:

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

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

My first guess to explain this behaviour would be that the Microsoft SQL Server builds a execution plan once, which scans all tables, and reuses this for every execution of this query, thus is scanning all tables at all times.

Does anyone know how we could get the SQL Server to only scan the necessary tables and still use a parameterized filter? Or can anyone confirm that this is a bug in the SQL Server execution plan builder?

For the full code have a look at this SQL Fiddle: http://sqlfiddle.com/#!6/e1f33/1

Answer 1

Generally speaking, OR predicates are challenging for SQL Server to optimize and generate a reusable cached plan.

I ran the query with the OPTION(RECOMPILE) query hint and the actual execution plan shows unneeded partitioned view member tables are statically eliminated from the plan. Not sure why sqlfiddle doesn't show this (it's currently using SQL 2014 RTM) but I observed elimination with all versions of SQL Server from 2012 through 2017 RC2 with latest service packs installed.

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

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

Here's the actual execution plan 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>