Decrypting Insert Query Plans

Last Updated 6 years ago

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.

IF  (OBJECT_ID('crap')) IS NOT NULL
BEGIN
DROP SEQUENCE dbo.crap

CREATE SEQUENCE dbo.crap 
AS BIGINT
START WITH 1
INCREMENT BY 1
NO CYCLE
CACHE 10000
END

CREATE TABLE dbo.ident_crap (Id BIGINT IDENTITY (1,1), cruft BIGINT DEFAULT 9223372036854775807)

CREATE TABLE dbo.sequence_crap (Id BIGINT DEFAULT NEXT VALUE FOR dbo.crap, cruft BIGINT DEFAULT 9223372036854775807)

CREATE TABLE dbo.newid_crap (Id UNIQUEIDENTIFIER DEFAULT NEWID(), cruft BIGINT DEFAULT 9223372036854775807)

CREATE TABLE dbo.newseqid_crap (Id UNIQUEIDENTIFIER DEFAULT NEWSEQUENTIALID(), cruft BIGINT DEFAULT 9223372036854775807)

INSERT dbo.ident_crap ( cruft )
DEFAULT VALUES   

INSERT dbo.sequence_crap ( Id, cruft )
DEFAULT VALUES   

INSERT dbo.newid_crap ( Id, cruft )
DEFAULT VALUES   

INSERT dbo.newseqid_crap ( Id, cruft )
DEFAULT VALUES   

DROP TABLE dbo.ident_crap, dbo.sequence_crap, dbo.newid_crap, dbo.newseqid_crap

IF (OBJECT_ID('crap')) IS NOT NULL

BEGIN

DROP SEQUENCE dbo.crap

CREATE SEQUENCE dbo.crap

AS BIGINT

START WITH 1

INCREMENT BY 1

NO CYCLE

CACHE 10000

END

CREATE TABLE dbo.ident_crap (Id BIGINT IDENTITY (1,1), cruft BIGINT DEFAULT 9223372036854775807)

CREATE TABLE dbo.sequence_crap (Id BIGINT DEFAULT NEXT VALUE FOR dbo.crap, cruft BIGINT DEFAULT 9223372036854775807)

CREATE TABLE dbo.newid_crap (Id UNIQUEIDENTIFIER DEFAULT NEWID(), cruft BIGINT DEFAULT 9223372036854775807)

CREATE TABLE dbo.newseqid_crap (Id UNIQUEIDENTIFIER DEFAULT NEWSEQUENTIALID(), cruft BIGINT DEFAULT 9223372036854775807)

INSERT dbo.ident_crap ( cruft )

DEFAULT VALUES

INSERT dbo.sequence_crap ( Id, cruft )

DEFAULT VALUES

INSERT dbo.newid_crap ( Id, cruft )

DEFAULT VALUES

INSERT dbo.newseqid_crap ( Id, cruft )

DEFAULT VALUES

DROP TABLE dbo.ident_crap, dbo.sequence_crap, dbo.newid_crap, dbo.newseqid_crap

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.

CREATE TABLE dbo.crap_ck (Id BIGINT IDENTITY(1,1) PRIMARY KEY CLUSTERED, cruft BIGINT NOT NULL CHECK (cruft > 0 AND cruft < 11) )

INSERT dbo.crap_ck ( cruft )
    VALUES ( 1 )

DROP TABLE dbo.crap_ck

CREATE TABLE dbo.crap_ck (Id BIGINT IDENTITY(1,1) PRIMARY KEY CLUSTERED, cruft BIGINT NOT NULL CHECK (cruft > 0 AND cruft < 11) )

INSERT dbo.crap_ck ( cruft )

VALUES ( 1 )

DROP TABLE dbo.crap_ck

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.

It’s always a CASE expression, isn’t it?

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.

SELECT *
FROM dbo.crap_ck AS cc
WHERE cc.cruft > 11
AND 1=1

SELECT *
FROM dbo.crap_ck AS cc
WHERE cc.Id = 1 AND cc.Id = 10
AND 1=1

SELECT *

FROM dbo.crap_ck AS cc

WHERE cc.cruft > 11

AND 1=1

SELECT *

FROM dbo.crap_ck AS cc

WHERE cc.Id = 1 AND cc.Id = 10

AND 1=1

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.

CREATE TABLE dbo.crap_pk (Id BIGINT IDENTITY (1,1) PRIMARY KEY CLUSTERED, cruft BIGINT NOT NULL)

CREATE TABLE dbo.crap_fk (Id BIGINT IDENTITY (1,1) PRIMARY KEY CLUSTERED, cruft BIGINT NOT NULL, Id_fk BIGINT NOT NULL CONSTRAINT idfk FOREIGN KEY REFERENCES dbo.crap_pk(Id))

INSERT dbo.crap_pk ( cruft )
SELECT 1

INSERT dbo.crap_fk ( cruft, Id_fk )
SELECT 1, 1

DROP TABLE dbo.crap_fk, dbo.crap_pk

CREATE TABLE dbo.crap_pk (Id BIGINT IDENTITY (1,1) PRIMARY KEY CLUSTERED, cruft BIGINT NOT NULL)

CREATE TABLE dbo.crap_fk (Id BIGINT IDENTITY (1,1) PRIMARY KEY CLUSTERED, cruft BIGINT NOT NULL, Id_fk BIGINT NOT NULL CONSTRAINT idfk FOREIGN KEY REFERENCES dbo.crap_pk(Id))

INSERT dbo.crap_pk ( cruft )

SELECT 1

INSERT dbo.crap_fk ( cruft, Id_fk )

SELECT 1, 1

DROP TABLE dbo.crap_fk, dbo.crap_pk

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

This is why people tell you to index your foreign keys

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!

CTEs, Views, and NOLOCK

Checking for Strange Client Settings with sys.dm_exec_sessions

6 Comments. Leave new

Ron S
March 30, 2017 11:36 am

Interesting information. The article was a bit ‘crappy’ though. 🙂

Reply
- Erik Darling
  March 30, 2017 1:04 pm
  
  That’s what I aim for!
  
  Reply
Morden Kain
March 30, 2017 1:15 pm

Yup, on that FK problem, it is not just indexing, it is also ensuring that you use a ALTER TABLE CHECK CHECK CONSTRAINT after a trunc and reload of that data. I got tired of telling the database developers about the problem and created a job that runs once a day before they come in. Makes the developers wonder wth was happening the prior day.

Reply
- Erik Darling
  March 30, 2017 1:49 pm
  
  You RASCAL
  
  Reply
  - Morden Kain
    March 30, 2017 6:08 pm
    
    Yeah, it is the little things that makes them think they are going bonkers. Took one developer two weeks before they asked me why their errors would come up in Russian and every time they logged into SQL Server they were magically transported to a database they never used. I am currently plotting my next move…
    
    Reply
What happens on an INSERT? – Steve Henry's SQL Server Tips
March 5, 2019 6:25 am

[…] https://www.brentozar.com/archive/2017/03/decrypting-insert-query-plans/ […]

Reply