Partition Table using min/max functions and Top N - Index selection and performance by Mark Harvey1

Correction: the final queries in my workaround below should be these (the point is to do true TOP on each partition and only scan as little of the index as needed to get the top N values).

-- does a full scan
select top 3 *
from t
order by a;
go

-- This query gets a plan that uses a top on each partition, and then another top
-- over that to get the final result.
select top 3 o.*
from (values (1),(2), (3)) as p(a) cross apply
     (select top 3 *
        from t
        where $partition.pf(t.b) = p.a
        order by t.a asc) as o
order by o.a;

Using the new "VALUES" clause and an outer apply, you can work around the issue with a single query in some cases. See below. I know, it is inconvenient but it can get you much faster performance.

use tempdb;
go

create partition function pf(int) as range left for values (100000,200000,300000);
go

create partition scheme ps as partition pf ALL TO ( [PRIMARY] );
go

create table t(a int, b int) on ps(b);
create clustered index idx on t(a);

go

set nocount on;
declare @i int;
set @i = 1;
while (@i<>10000)
begin
    insert t values(@i*100, (@i*100 + 200000) % 89890)
    set @i += 1;
end
go

select top 3 *
from t
order by b;
go

-- This query gets a plan that uses a top on each partition, and then another top
-- over that to get the final result.
select top 3 o.*
from (values (1),(2), (3)) as p(a) cross apply
     (select top 3 *
        from t
        where $partition.pf(t.b) = p.a
        order by t.b) as o
order by o.b;

I was finally able to figure out a workaround-- see below for an example on my database schema, although the approach is generally applicable. It's ugly but fast: create a temp table, loop through every partition and get the MAX WHERE $Partition = PartitionNumber, and then compute the MAX of the resulting temp table. As long as the each query is limited to one partition, you sidestep the bug.

-- DECLARE @StartingNewVisitId int
-- SELECT @StartingNewVisitId = ISNULL ((SELECT MIN (VisitId) FROM Traffic WITH (NOLOCK, INDEX(NIX_Traffic_Visit)) ), 0)
-- Instead, have to iterate through each partition to get the MAX
DECLARE @Partition int, @MaxPartition int

SELECT @Partition = (SELECT TOP 1 $PARTITION.TrafficMonthYear(RequestDateTime)
FROM Traffic ORDER BY RequestDateTime)

SELECT @MaxPartition = (SELECT TOP 1 $PARTITION.TrafficMonthYear(RequestDateTime)
FROM Traffic ORDER BY RequestDateTime DESC)

CREATE TABLE #Maxes (VisitId int)

WHILE (@Partition <= @MaxPartition)
BEGIN

INSERT INTO #Maxes
SELECT MAX (VisitId) as VisitId
FROM Traffic (NOLOCK)
WHERE $PARTITION.TrafficMonthYear(RequestDateTime) = @Partition

SET @Partition = @Partition + 1

END

DECLARE @StartingNewVisitId int

SELECT @StartingNewVisitId = ISNULL (MAX (VisitID), 0) + 1 FROM #Maxes

DROP TABLE #Maxes