Trigger on partitioned table and performance

I'm not sure if what I've found is a bug, but it certainly looks like it. I wasn't able to find much information about it, so I've decided I'll put it here.

So, in a nutshell, I'm facing terrible performance when accessing internal tables (inserted and deleted) in the trigger defined on partitioned table.

To test the issue I've created some simple tables, exactly the same, but one is partitioned and another is not:

create table [dbo].[Test1](
    [part_id] [int] not null,
    [id] [int] not null,
    [cost] [float] null,
    constraint [pk__Test1] primary key clustered ([part_id] asc, [id] asc) on ps_part(part_id)
);

create table [dbo].[Test2](
    [part_id] [int] not null,
    [id] [int] not null,
    [cost] [float] null,
    constraint [pk__Test1] primary key clustered ([part_id] asc, [id] asc)
);

Then I've filled the tables with some data. I don't have a data generation script right now, I've just used some local data, but there're about 473 different partitions and around 383M rows in these tables.

Then I've just tested how fast updates on these tables are, with very simple queries like

update dbo.Test1 set cost = cost + 0.1 where part_id = ??;
update dbo.Test1 set cost = cost - 0.1 where part_id = ??;

update dbo.Test2 set cost = cost + 0.1 where part_id = ??;
update dbo.Test2 set cost = cost - 0.1 where part_id = ??;

And results were logical - average time of update for partitioned table was around 2 seconds, and average time of update for non-partitioned table was around 4 seconds.

Then I've created simple triggers on both tables

alter trigger [dbo].[Test1__changed] on [dbo].[Test1]
after insert,update,delete
as 
begin
    set nocount on;

    select a.part_id
    into #temp11111111
    from (
        select r.part_id from inserted as r
        union
        select r.part_id from deleted as r
    ) as a;
end

After that I've tried the same test query, and results were really strange - on partitioned table it took on avera

This issue is documented for SQL Server 2012 in KB 2606883 found by Alex.

I repro-ed the issue in SQL Server 2019 and validated that setting trace flag 2470 resolved it.

I'm unclear exactly what the nature of the fix is and why this isn't the default behaviour of the product in later versions.

For my example data (30 million rows evenly distributed over 700 partitions with one empty partition) this brought the time taken for the Inserted Scan and Deleted Scan down from > 4 seconds each to 0.021 seconds each.

Execution plans (including runtime stats) with the TF on and off are here. In both plans the deleted/inserted scans still say they access all 701 partitions.

StackExchange Database Administrators Q#314094, answer score: 8