Trivial Plans, Simple Parameterization and Check Constraints

Trivial Plans Are Weird

They’re the optimizer’s “Yes, Dear”.  The thing is, sometimes “Yes, Dear” can miss some important details.

  • Cost based decisions about operators
  • Potential index improvements
  • Contradiction detection for check constraints

Simple parameterization, according to… uh… Official Microsoft documentation? Can only occur in a Trivial Plan.

Which makes sense. Simple parameterization is considered and attempted only when the optimizer thinks that the predicate changing would not have a drastic effect on the query plan.

But That’s Not Always True

Let’s start with a check constraint on the Users table. This is the 2010 data before Mr. Skeet hit 1000k reputation, so it succeeds, and is trusted.

You can verify if constraints are trusted or not by querying the sys.check_constraints view:

Let’s take this query for example:

There’s an obvious problem

Our check constraint should negate our where clause. We shouldn’t need to touch the table at all.

To make things worse, we end up scanning the entire thing.

Bogus.

If we add something goofy to our query, we get the correct plan:

Of course 1’s gonna be in there. It’s 1. We’re selecting 1.  But this is enough to make the optimizer reflect on its choices.

Hustle real hard

Extra Crispy

I know what you’re thinking. I can read your future mind. You think that adding an index would help.

Okay, let’s add an index that covers the entire query.

Same problem.

To make matters worse, if we add a non-covering index…

We get the plan we want, but yet another plan that lies to us about Simple Parameterization.

%$&^%

But Why?

The easy answer is that with a cost based decision to make, the optimizer now has to explore optimizations beyond a trivial plan.

Now it has a choice between

  • Scanning the Clustered Index
  • Seeking into the index on Reputation, and doing a Key Lookup for DisplayName and Age

Comparing that to when the nonclustered index covered the entire query, there was no cost-based alternative. A trivial plan to use a covering index makes the most sense.

To show an absurd example, with the same single key column on Reputation, we can force the optimizer to use it for a query where it would have no benefit whatsoever.

Foolish~

Now I know. This decision is a no-brainer. But it’s still a decision, and one the optimizer has to weigh the cost of each, with no exact matching index.

Fun, right?

Thanks for reading!

Brent says: when you run sp_BlitzCache, you might have noticed the “Trivial” warning in the Warnings column. This is a great example of why we call it out – the query might be in your top 10 most resource-consuming queries not because it’s impossible to get a better query plan for it, but just because SQL Server chose not to take the time to evaluate better plans. Normally, you’ll never have to troubleshoot performance on a trivial query – but once it hits your top 10, that’s when it’s time to dig a little deeper.

Previous Post
Building SQL ConstantCare®: Adding Daily Emails and Recognizing Your Work
Next Post
How to Reduce the CPU Overhead of Dynamic SQL

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.

Menu
{"cart_token":"","hash":"","cart_data":""}