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.
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:
1 | SELECT name, cc.is_not_trusted FROM sys.check_constraints AS cc; |
Let’s take this query for example:
1 2 3 | 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.
1 | Table 'Users'. Scan count 1, logical reads 7345 |
Bogus.
If we add something goofy to our query, we get the correct plan:
1 2 3 4 | 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.
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…
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.
1 2 3 4 5 6 7 | 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.
