Trigger on partitioned table and performance

Question

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 average 3 minutes for the query to finish, while on non-partitioned table the timing was similar to what it was without the triggers - about 4 seconds.

Do you know why this might happen? Any way to workaround this problem?

Answer 1

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.

Answer 2

To add to Martin's answer concerning the nature of the fix:

Without the fix, row versions generated to support the trigger are accessed via a single storage engine rowset handle associated with the transaction.

The scan of the inserted and deleted pseudo-tables is marked as partitioned. Each partition is handled by scanning the version list via the single rowset handle. The version list is scanned 700 times in the example.

When the statement that caused the trigger to fire affects only a few partitions (or a single partition as in the example) this results in a lot of unnecessary version scanning. In the example, all but one of the version scans will find no matching records.

The trigger itself is a separate execution. It doesn't know anything about the set of partitions affected by the triggering statement.

The fix under TF 2470 creates a storage engine rowset handle per partition, but only when the underlying partitioned object has 100 partitions or more. The number of handles is scaled up to the nearest power of two (giving 128 handles for 100 partitions, for example).

The inserted and deleted scans inside the trigger still access all partitions. The per-partition rowset handles make this much more efficient by only accessing versions associated with a particular partition, instead of the entire set each time.

This change occurs at a very low level (sqlmin!RowsetVersionScan::Init) inside the storage engine and is not visible through any DMVs. It is quite a risky change given all the ways triggers can interact with other engine features, so it is protected by a trace flag and not on by default. Users affected by the issue can enable the flag, and quickly revert if necessary.