A Bit About Trivial Plans

A Trivial plan is created when SQL Server really doesn’t have any choice in how it’s going to execute. Here’s an example from the StackOverflow database with the indexes removed:

SELECT *
FROM dbo.Users
WHERE Id = 1234;

There really isn’t any choice but a clustered index seek. It’s the only index and its about as effective as we can get given we’re filtering on the clustering key for a specific value:

To see the optimisation level for a query we can look at the properties for the execution plan – either estimated or actual:

Trivial plans can even be seen for more complex queries under the right circumstances:

SELECT
	DISTINCT
	TOP (100)
	Id,
	DisplayName,
	ROW_NUMBER() OVER (PARTITION BY Id ORDER BY Id)
FROM dbo.Users;

Even with a DISTINCT and TOP clause, as well as a ROW_NUMBER function this still produces a Trivial plan. Admittedly this is a very tailored example based on the knowledge that our Id field is our primary key and therefore unique – which renders the DISTINCT and ROW_NUMBER functions irrelevant.

It doesn’t take a lot to make plans less-trivial. Let’s take this query:

SELECT TOP (50) *
FROM dbo.Users
WHERE Reputation = 10000;

Even though it will need to scan the whole table and returns no rows it’s still trivial. That’s because the engine thinks there are 59 records and we only want 50 of them so the scan isn’t too expensive. If we increase that number though:

SELECT TOP (60) *
FROM dbo.Users
WHERE Reputation = 10000;

That increase has pushed the cost for our query over my threshold for parallelism so the engine does now have a choice to make. That brings with it full optimisation:

Parallelism is a choice but will only be considered once the cost threshold has been breached. We could also give the engine another choice with an index:

CREATE INDEX Age
ON dbo.Users (Age);

With that in place we don’t even need to do a lot of work any more:

SELECT TOP (1) *
FROM dbo.Users
WHERE Reputation = 10000;

This index won’t help us to execute this query, but it gives the engine an alternative. The plan may look the same as our TOP 50 previously but not it’s fully optimised. The choice was easy but it was available. It was a better option than this mess:

Duh.

So Trivial plans are where we don’t have any other option so it’s all fine, right? Well, maybe. There’s a reason it’s flagged up specifically within sp_BlitzCache and it’s not because they’re trivial but what it can indicate.

Two of the more impactful issues which excessive trivial plans can cause are:

1. A trivial plan won’t provide any missing index recommendation as it doesn’t process the query enough to evaluate their impact. If you’re relying on these missing indexes to help optimise your environment this can leave a gap in your visibility.
2. Having a large number of trivial queries being ran or plans in your cache that can signal issues. Not in what they’re doing but how much. As it may not be possible to optimise the trivial plans, you may need to reduce the calls, cache results, or look at other methods for reducing the frequency of their use.

Wrap up

In this post we’ve looked at Trivial execution plans. They’re the engine having little to no choice in how it decides to execute a query so it doesn’t need to take too long mulling it over.

Trivial plans aren’t inherently bad or a sign of bad things happening. They can be a benefit to keep a plan simple when there’s no viable alternative that should be considered.

Where we see an excessive number of trivial plans it may be different. It could indicate action being needed to understand and act on the root cause for them.

In terms of identifying those statements I’ll direct you to the concise supporting documentation from sp_BlitzCache which contains a handy query snippet to get you started.