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.
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
DROP SEQUENCE dbo.crap
CREATE SEQUENCE dbo.crap
START WITH 1
INCREMENT BY 1
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 )
INSERT dbo.sequence_crap ( Id, cruft )
INSERT dbo.newid_crap ( Id, cruft )
INSERT dbo.newseqid_crap ( Id, cruft )
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.
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
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.
FROM dbo.crap_ck AS cc
WHERE cc.cruft > 11
FROM dbo.crap_ck AS cc
WHERE cc.Id = 1 AND cc.Id = 10
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 )
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.
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.
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!