5

I have a partitioned table and problems with partition elimination.

Setup

My table if quite simple and looks like this:

CREATE TABLE [data].[OperationData](
    [DataLoggerID] [bigint] NOT NULL,
    [End] [datetime2](7) NOT NULL
CONSTRAINT [PK_OperationData] PRIMARY KEY CLUSTERED 
(
    [DataLoggerID] ASC,
    [End] ASC
))

The table is partitioned on [End] column by date.

Problem

I execute a simple query to find the newest row by [End] column.

SELECT TOP(1)
    [OperationData].[DataLoggerID],
    [OperationData].[End]
FROM [data].[OperationData]
WHERE [OperationData].[DataLoggerID] = 30217  
ORDER BY [OperationData].[End] DESC

The problem is that the execution plan shows that all partitions are searched and no partition elimination has happened.

stats

Question

Is partition elimination possible on this query? And when yes why it is not used?

Some more...

I can't paste the plan on "Paste The Plan" (it shows an error) so I copy it in here.

<?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.520" Build="15.0.400.390" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
  <BatchSequence>
    <Batch>
      <Statements>
        <StmtSimple StatementCompId="1" StatementEstRows="1" StatementId="1" StatementOptmLevel="FULL" CardinalityEstimationModelVersion="130" StatementSubTreeCost="26.2112" StatementText="SELECT TOP(1)&#xD;&#xA;    [OperationData].[DataLoggerID],&#xD;&#xA;   [OperationData].[End]&#xD;&#xA;FROM [data].[OperationData] AS [OperationData]  &#xD;&#xA;WHERE [OperationData].[DataLoggerID] = 30217  &#xD;&#xA;ORDER BY [OperationData].[End] DESC" StatementType="SELECT" QueryHash="0xCA9C86DFB3F9CA37" QueryPlanHash="0xB953A3686656A27D" RetrievedFromCache="true" StatementSqlHandle="0x090060DDDE8D2D2A050D6AF14FDF6902EDD10000000000000000000000000000000000000000000000000000" DatabaseContextSettingsId="7" ParentObjectId="0" StatementParameterizationType="0" 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="0" NonParallelPlanReason="MaxDOPSetToOne" MemoryGrant="1024" CachedPlanSize="280" CompileTime="6783" CompileCPU="52" CompileMemory="7848">
            <MissingIndexes>
              <MissingIndexGroup Impact="95.7193">
                <MissingIndex Database="[powerdoo]" Schema="[data]" Table="[OperationData]">
                  <ColumnGroup Usage="EQUALITY">
                    <Column Name="[DataLoggerID]" ColumnId="1" />
                  </ColumnGroup>
                </MissingIndex>
              </MissingIndexGroup>
            </MissingIndexes>
            <Warnings>
              <MemoryGrantWarning GrantWarningKind="Excessive Grant" RequestedMemory="1024" GrantedMemory="1024" MaxUsedMemory="16" />
            </Warnings>
            <MemoryGrantInfo SerialRequiredMemory="16" SerialDesiredMemory="24" RequiredMemory="16" DesiredMemory="24" RequestedMemory="1024" GrantWaitTime="0" GrantedMemory="1024" MaxUsedMemory="16" MaxQueryMemory="3104936" />
            <OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="5872010" EstimatedPagesCached="1101001" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="9384056" />
            <WaitStats>
              <Wait WaitType="SOS_SCHEDULER_YIELD" WaitTimeMs="805" WaitCount="19" />
              <Wait WaitType="RESOURCE_GOVERNOR_IDLE" WaitTimeMs="15" WaitCount="8" />
            </WaitStats>
            <QueryTimeStats CpuTime="76" ElapsedTime="846" />
            <RelOp AvgRowSize="23" EstimateCPU="1.00318" EstimateIO="0.0112613" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="TopN Sort" NodeId="0" Parallel="false" PhysicalOp="Sort" EstimatedTotalSubtreeCost="26.2112">
              <OutputList>
                <ColumnReference Database="[powerdoo]" Schema="[data]" Table="[OperationData]" Alias="[OperationData]" Column="DataLoggerID" />
                <ColumnReference Database="[powerdoo]" Schema="[data]" Table="[OperationData]" Alias="[OperationData]" Column="End" />
              </OutputList>
              <MemoryFractions Input="1" Output="1" />
              <RunTimeInformation>
                <RunTimeCountersPerThread Thread="0" ActualRebinds="1" ActualRewinds="0" ActualRows="1" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="845" ActualCPUms="76" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" InputMemoryGrant="1024" OutputMemoryGrant="1024" UsedMemoryGrant="16" />
              </RunTimeInformation>
              <TopSort Distinct="false" Rows="1">
                <OrderBy>
                  <OrderByColumn Ascending="false">
                    <ColumnReference Database="[powerdoo]" Schema="[data]" Table="[OperationData]" Alias="[OperationData]" Column="End" />
                  </OrderByColumn>
                </OrderBy>
                <RelOp AvgRowSize="23" EstimateCPU="1.22175" EstimateIO="23.975" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="15680.7" EstimatedRowsRead="15680.7" LogicalOp="Clustered Index Seek" NodeId="1" Parallel="false" Partitioned="true" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="25.1968" TableCardinality="81308300">
                  <OutputList>
                    <ColumnReference Database="[powerdoo]" Schema="[data]" Table="[OperationData]" Alias="[OperationData]" Column="DataLoggerID" />
                    <ColumnReference Database="[powerdoo]" Schema="[data]" Table="[OperationData]" Alias="[OperationData]" Column="End" />
                  </OutputList>
                  <RunTimeInformation>
                    <RunTimeCountersPerThread Thread="0" ActualRows="49050" ActualRowsRead="49050" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="730" ActualCPUms="64" ActualScans="7672" ActualLogicalReads="2533" ActualPhysicalReads="0" ActualReadAheads="65" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
                  </RunTimeInformation>
                  <RunTimePartitionSummary>
                    <PartitionsAccessed PartitionCount="7672">
                      <PartitionRange Start="1" End="7672" />
                    </PartitionsAccessed>
                  </RunTimePartitionSummary>
                  <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
                    <DefinedValues>
                      <DefinedValue>
                        <ColumnReference Database="[powerdoo]" Schema="[data]" Table="[OperationData]" Alias="[OperationData]" Column="DataLoggerID" />
                      </DefinedValue>
                      <DefinedValue>
                        <ColumnReference Database="[powerdoo]" Schema="[data]" Table="[OperationData]" Alias="[OperationData]" Column="End" />
                      </DefinedValue>
                    </DefinedValues>
                    <Object Database="[powerdoo]" Schema="[data]" Table="[OperationData]" Index="[PK_OperationData]" Alias="[OperationData]" IndexKind="Clustered" Storage="RowStore" />
                    <SeekPredicates>
                      <SeekPredicateNew>
                        <SeekKeys>
                          <StartRange ScanType="GE">
                            <RangeColumns>
                              <ColumnReference Column="PtnId1000" />
                            </RangeColumns>
                            <RangeExpressions>
                              <ScalarOperator ScalarString="(1)">
                                <Const ConstValue="(1)" />
                              </ScalarOperator>
                            </RangeExpressions>
                          </StartRange>
                          <EndRange ScanType="LE">
                            <RangeColumns>
                              <ColumnReference Column="PtnId1000" />
                            </RangeColumns>
                            <RangeExpressions>
                              <ScalarOperator ScalarString="(7672)">
                                <Const ConstValue="(7672)" />
                              </ScalarOperator>
                            </RangeExpressions>
                          </EndRange>
                        </SeekKeys>
                        <SeekKeys>
                          <Prefix ScanType="EQ">
                            <RangeColumns>
                              <ColumnReference Database="[powerdoo]" Schema="[data]" Table="[OperationData]" Alias="[OperationData]" Column="DataLoggerID" />
                            </RangeColumns>
                            <RangeExpressions>
                              <ScalarOperator ScalarString="(30217)">
                                <Const ConstValue="(30217)" />
                              </ScalarOperator>
                            </RangeExpressions>
                          </Prefix>
                        </SeekKeys>
                      </SeekPredicateNew>
                    </SeekPredicates>
                  </IndexScan>
                </RelOp>
              </TopSort>
            </RelOp>
          </QueryPlan>
        </StmtSimple>
      </Statements>
    </Batch>
  </BatchSequence>
</ShowPlanXML>

2 Answers 2

11

The optimizer can produce an optimal plan for this query:

Sample schema

CREATE PARTITION FUNCTION PF (datetime2(7)) AS 
RANGE RIGHT FOR VALUES 
(
    '20180101','20180102','20180103','20180104','20180105','20180106','20180107','20180108','20180109','20180110',
    '20180111','20180112','20180113','20180114','20180115','20180116','20180117','20180118','20180119','20180120',
    '20180121','20180122','20180123','20180124','20180125','20180126','20180127','20180128','20180129','20180130',
    '20180131','20180201','20180202','20180203','20180204','20180205','20180206','20180207','20180208','20180209',
    '20180210','20180211','20180212','20180213','20180214','20180215','20180216','20180217','20180218','20180219',
    '20180220','20180221','20180222','20180223','20180224','20180225','20180226','20180227','20180228','20180301',
    '20180302','20180303','20180304','20180305','20180306','20180307','20180308','20180309','20180310','20180311',
    '20180312','20180313','20180314','20180315','20180316','20180317','20180318','20180319','20180320','20180321',
    '20180322','20180323','20180324','20180325','20180326','20180327','20180328','20180329','20180330','20180331',
    '20180401','20180402','20180403','20180404','20180405','20180406','20180407','20180408','20180409','20180410',
    '20180411','20180412','20180413','20180414','20180415','20180416','20180417','20180418','20180419','20180420',
    '20180421','20180422','20180423','20180424','20180425','20180426','20180427','20180428','20180429','20180430',
    '20180501','20180502','20180503','20180504','20180505','20180506','20180507','20180508','20180509','20180510',
    '20180511','20180512','20180513','20180514','20180515','20180516','20180517','20180518','20180519','20180520',
    '20180521','20180522','20180523','20180524','20180525','20180526','20180527','20180528','20180529','20180530',
    '20180531','20180601','20180602','20180603','20180604','20180605','20180606','20180607','20180608','20180609',
    '20180610','20180611','20180612','20180613','20180614','20180615','20180616','20180617','20180618','20180619',
    '20180620','20180621','20180622','20180623','20180624','20180625','20180626','20180627','20180628','20180629',
    '20180630','20180701'
);

CREATE PARTITION SCHEME PS 
AS PARTITION PF 
ALL TO ([PRIMARY]);

Sample data

CREATE TABLE [dbo].[OperationData]
(
    [DataLoggerID] [bigint] NOT NULL,
    [End] [datetime2](7) NOT NULL
    CONSTRAINT [PK_OperationData] PRIMARY KEY CLUSTERED 
    (
        [DataLoggerID] ASC,
        [End] ASC
    ) ON PS ([End])
);

-- Add one million rows
WITH
  L0   AS (SELECT 1 AS c UNION ALL SELECT 1),
  L1   AS (SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B),
  L2   AS (SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B),
  L3   AS (SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B),
  L4   AS (SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B),
  L5   AS (SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B),
  Nums AS (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS n FROM L5)
INSERT dbo.OperationData
    WITH (TABLOCKX)
    (DataLoggerID, [End])
SELECT
    Nums.n % 50000,
    DATEADD(DAY, Nums.n % 366, '20180101')
FROM Nums
WHERE
    Nums.n <= 1000 * 1000;

Original query

SELECT TOP (1)
    [OperationData].[DataLoggerID],
    [OperationData].[End]
FROM [dbo].[OperationData]
WHERE [OperationData].[DataLoggerID] = 30217  
ORDER BY [OperationData].[End] DESC;

The execution plan shows one backward ordered seek per partition, with the first qualifying row found returned:

Backward scan plan

Seek properties

This is exactly as you would hope: process each partition from the highest number down, immediately seek to find the highest End date & time for the given DataLoggerID, and stop after finding the first row.

This plan was found on Microsoft SQL Azure:

Version="1.520" Build="15.0.400.390"

I have also verified that the optimizer produces the same optimal plan for all versions from SQL Server 2008 R2 to SQL Server 2017. This includes testing with 15,000 partitions (where available).

Workaround

Otherwise, you can write the query logic more explicitly:

CREATE TABLE #Partitions (partition_number integer PRIMARY KEY);

INSERT #Partitions 
    (partition_number)
SELECT
    P.partition_number
FROM sys.partitions AS P
WHERE
    P.[object_id] = OBJECT_ID(N'dbo.OperationData', 'U')

SELECT TOP (1) 
    LocalMinimum.DataLoggerID,
    LocalMinimum.[End]
FROM #Partitions AS P
CROSS APPLY
(
    SELECT TOP (1)
        OD.DataLoggerID,
        OD.[End]
    FROM  dbo.OperationData AS OD
    WHERE
        $PARTITION.PF(OD.[End]) = P.partition_number
        AND OD.DataLoggerID = 30217
    ORDER BY
        OD.[End] DESC
) AS LocalMinimum
WHERE
    LocalMinimum.DataLoggerID = 30217
ORDER BY
    P.partition_number DESC,
    LocalMinimum.[End] DESC;

This gives the following plan to load the partition numbers into the temporary table:

Load partitions

And this plan for the per-partition query:

Explicit query

Once again, this processes partitions in reverse order until the first row is found.

2
  • @PaulWhite Now its getting strange, I tried your example and get the same result like you. Only one partition is scanned. BUT (!) my partition function, schema and table looks exactly like your example!? I don't understand why I get a different result. Commented Jun 3, 2018 at 22:44
  • 1
    @SteffenMangold I tried it with 15,000 partitions locally and the plan was the same optimal one. Perhaps it would be better if you can add detail to your question to more precisely define the environment and tables involved.
    – Paul White
    Commented Jun 4, 2018 at 2:54
1

Partition elimination is not possible here because the partition metadata does not have the information about whether there are any rows for that DataLoggerID in the head partition.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.