Partition elimination on order by

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.

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.

```

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:

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:

And this plan for the per-partition query:

Once again, this processes partitions in reverse order un

StackExchange Database Administrators Q#208603, answer score: 11