Trivial Plans, Simple Parameterization and Check Constraints

May 22, 2018

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.

    ALTER TABLE dbo.Users
        ADD CONSTRAINT cx_rep CHECK ( Reputation >= 1 AND Reputation <= 1000000 );

1 2	ALTER TABLE dbo.Users ADD CONSTRAINT cx_rep CHECK ( Reputation >= 1 AND Reputation <= 1000000 );

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

    SELECT name, cc.is_not_trusted FROM sys.check_constraints AS cc;

1	SELECT name, cc.is_not_trusted FROM sys.check_constraints AS cc;

Let’s take this query for example:

    SELECT u.DisplayName, u.Age, u.Reputation
    FROM dbo.Users AS u
    WHERE u.Reputation = 0;

SELECT u.DisplayName, u.Age, u.Reputation

FROM dbo.Users AS u

WHERE u.Reputation = 0;

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.

    Table 'Users'. Scan count 1, logical reads 7345

1	Table 'Users'. Scan count 1, logical reads 7345

Bogus.

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

    SELECT u.DisplayName, u.Age, u.Reputation
    FROM dbo.Users AS u
    WHERE u.Reputation = 0
    AND 1 IN (SELECT 1);

SELECT u.DisplayName, u.Age, u.Reputation

FROM dbo.Users AS u

WHERE u.Reputation = 0

AND 1 IN (SELECT 1);

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.

    CREATE INDEX ix_reputation ON dbo.Users(Reputation) INCLUDE(DisplayName, Age);

1	CREATE INDEX ix_reputation ON dbo.Users(Reputation) INCLUDE(DisplayName, Age);

Same problem.

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

    CREATE INDEX ix_reputation_eh ON dbo.Users(Reputation);

1	CREATE INDEX ix_reputation_eh ON dbo.Users(Reputation);

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.

    SELECT TOP 100 *
    FROM dbo.Users AS u
    WHERE u.DisplayName LIKE 'Eggs%'

    SELECT TOP 100 *
    FROM dbo.Users AS u WITH (INDEX = ix_reputation_eh)
    WHERE u.DisplayName LIKE 'Eggs%'

SELECT TOP 100 *

FROM dbo.Users AS u

WHERE u.DisplayName LIKE 'Eggs%'

SELECT TOP 100 *

FROM dbo.Users AS u WITH (INDEX = ix_reputation_eh)

WHERE u.DisplayName LIKE 'Eggs%'

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.