Tricky triggers

In my last entry I noted that the performance of "instead-of" triggers can be tricky.

One of the reasons for this is due to the way the engine estimates the number of rows in the inserted and deleted pseudotables. This post will be a quick demonstration of that.

Demo methodology

I'll create three tables, t, u, and v:

1. t will be a source of 50,000 rows
2. I will insert those rows to table u, and an "after insert" trigger will count the number for inserted rows
3. I will then do the same insert against table v, and an "instead of insert" trigger will do the same count

Then we'll take a look at the plans. Here's the setup.

create table t(i int primary key clustered, c char)
-- put 50000 rows into t
insert      t(i, c)
select      top 50000             
            row_number() over (order by i.object_id), 'a'
from        sys.all_objects i
cross join  sys.all_objects j
go
create table u(i int primary key clustered, c char)
go
create or alter trigger after_count on u for insert as begin
    declare @i int      
    select  @i = count(*)
    from    inserted
end
go
create table v(i int primary key clustered, c char)
go
create or alter trigger instead_count on v instead of insert as begin
    declare @i int
    select  @i = count(*)
    from    inserted
end
go

Analysis

OK, let's do the insert and look at the plans. Click on the image on the right for a full size version

insert u(i, c) select i, c from t
insert v(i, c) select i, c from t

So, what's going on in these plans?

First, the four plans displayed are...

1. The insert into table u
2. Execution of the "after" trigger on u
3. The insert into table v
4. Execution of the "instead" trigger on v

The "inserted scan" for the "after" trigger is estimating that there are 50,000 rows in the inserted table. That's good, because there are!

The "clustered index seek" at the start of the "instead of" trigger (plan 4) is basically doing the same thing, ie, reading the inserted table. But why a seek, what's it seeking on? As I have indicated, the seek predicate is looking for the value "4" for something called "Act". What is this thing?

Look up one plan, and we see that in plan 3 there's a "compute scalar" operation that doesn't appear in plan 1 (the insert plan for the table with the "after" trigger). This is a special value created because of the existence of the "instead-of" trigger. Inserted rows get a value of 4. Deleted rows would have gotten a value of 3, but of course there are no deleted rows since we're doing an insert.

Fine then, the trigger is looking for inserted rows in the "inserted" table, a functionally equivalent operation to the "inserted scan" in plan 1. But look at the cardinality estimate! 223 rows estimated for our actual count of 50,000. That's not great. If I increase the insert count to 100,000 rows, the estimate goes to roughly 300. 400,000 rows are estimated as ~400. Clearly some kind of logarithmic estimate is going on here, but I don't know why.

Nevermind why though, can it be fixed?

Solutions

Yes, yes it can

In SQL 2014 the engine got a fancy new cardinality estimator. There's lots of articles on the web about this by people who know much more than myself, so I'm not going to discuss that per se. But what I will tell you is that the old cardinality estimator seems to work better for instead-of triggers.

You can tell the engine to use the old version. As far as I know there are 3 ways to do it.

1. Change the database compatibility level to 110 or lower
2. option(querytraceon 9481)
3. option(use hint('FORCE_LEGACY_CARDINALITY_ESTIMATION')) (SQL 2016+)

Changing the compatibility level of the database is a bit of a sledgehammer solution, and queries incorporating "querytraceon" can only be executed by sysadmins. But "use hint" is available to everyone. So as long as you're on 2016, that's certainly the best solution.

If you're not on 2016 then you can look into adding "execute as" to your trigger and using the querytraceon hint. But you should probably understand what invoking "execute as" entails. The best way to do this is by reading Erland Sommarksog's comprehensive guide. That would look something like this:

-- the owner of this database is 'sa'
create or alter trigger instead_count on v with execute as 'dbo' instead of insert as begin
    declare @i int
    select  @i = count(*)
    from    inserted 
    option  (querytraceon 9481)
end
go

Using any of these approaches he estimated number of rows for the inserted seek exactly matches the actual number of rows, at least in all cases that I've tried. Obviously the use hint approach is the least intrusive of the three.

A word of warning, though: the cardinality estimate will apply to the whole query, not just the part that needs to use the inserted table! This might be OK in a trigger, since the pseudotables are often the core of your logic and expense, but keep a watchful eye on your query plans!