We’ve all been there

Staring at a simple query that has a three mile long execution plan. After the first time, you learn to always look to make sure there’s not a secret view involved.

After looking at query plans for modifications involving indexed views, I decided to look at some other fairly common table design items that cause execution plan oddities. I’m focusing on INSERTs here, because… well, because I feel like it. Maybe UPDATEs and DELETEs will be other blog posts. I hate making promises like that, because then when I sit down to write them, I don’t find anything all that interesting.

Various defaults

We’ll look at two kinds of defaults here. Ones that rely on outside functions to supply a value, and hardcoded values. For outside functions, I picked a few of the obvious ones: Identity, Sequences, NEWID(), and NEWSEQUENTIALID().

We have to create a Sequence here, which only works on 2012+ — the definition isn’t all that important.

Tables, right? Am I right, people? Tables. What happens when we insert stuff?

The INSERT plan generates two Compute Scalar operators. One of them is simply sticking the default value in, but the other calls a function called getidentity, which calculates the next value in the identity column. I mean, I’m guessing that it does that. If it does something else, well, there goes my credibility!

Now, just hovering over the Compute Scalars doesn’t tell you anything about them. You have to highlight the one you’re interested in and hit f4 to bring up the Properties window over on the right side of SSMS. That’ll get you something that looks about like this.

I’m only showing this one once, because it’s the same every time it shows up. If you really need to verify that, play along with the code at home.

You’re fun looking. Check out all those parenthesis.

Inserting into the table with the Sequence generates the same exact two Compute Scalar plan, but naturally it calls the Sequence we defined to pull the next value instead of getidentity.

Now, this one threw me off a little bit. The plan is simplified to two operators.

Even though it still calls a function to generate the NEWID() value, and produces a scalar value for the default value, the query plan for some reason chooses not to tell us about them. I’ll have to do some other testing on why this is.

The NEWSEQUENTIALID() insert plan is back to the same ‘wide’ plan as the others. And, as is expected, it references that function on insert.

That hopefully clears up some of the more mundane reasons for additional query plan operators during INSERT. Not every Compute Scalar is an awful udf — though they can be that too, and sp_BlitzCache will warn you about that.

More interesting

Two other constructs that can cause additional operators are check constraints and foreign keys. Did you know that SQL Server doesn’t honor foreign keys on temp tables? That seems like something I should have known, but I guess I’d never tried it before. When I set up this demo with temp tables, I got this message and had to change a bunch of stuff.

Skipping FOREIGN KEY constraint ‘idfk’ definition for temporary table. FOREIGN KEY constraints are not enforced on local or global temporary tables.

Anyway, let’s start with a simple check constraint.

This plan is a bit more interesting. We get a Constant Scan, which is SQL Server’s way of telling us it’s creating one or more rows to do something with later. I’ll give you a bonus plan with a more interesting use for them in a minute.

The check constraint logic is housed in the Assert operator.

This is the thing that will throw an error if you try to insert an invalid value.

So, that more interesting use of Constant Scan? It can crop up during a phase of query optimization called contradiction detection. That’s when SQL Server tells you that your logic is broken and doesn’t even really bother making a fully formed querry babby. It can kick in for a whole bunch of reasons, but here are a couple examples.

The 1=1 is there to avoid getting a trivial plan, which ruined my demo at first. The 1=1 (for now) prevents SQL from creating and using a trivial plan for our query. I learned that trick from Kendra who learned it from Paul White. It’s pretty neat, and it makes SQL Server really think about what it’s doing. Upon thinking, it realizes neither of those two queries will return anything, so it shortcuts doing a whole bunch of work just to return 0 rows.

You’ve come a long way, baby

So here’s what foreign keys can do to query plans.

Ready for this? It goes and looks at the whole other table. Teehee.

The magic is in the Join and the Assert.

Again, this is what throws an error when your foreign keys don’t line up. Yeehaw.

Insertresting

Next time you look at an Insert query plan that has all sorts of horrible things going on in it, I hope you think of me. And I hope this blog post helps you figure out what all is going on with them.

Thanks for reading!