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 (
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?