Trivial Plans Are Not Always the Best Plans

When you execute a query, the SQL Server query optimizer determines potential query plans and decides which plan is the cheapest. It’s possible for a query to be so simple that there’s no need to bother weighing out different plans before returning results. In this case, SQL Server may use a trivial plan. Let’s look at an example of this plan and one reason why simple may not always be the best.

Cold Wind in August

Using the StackOverflow2013 database, let’s say we want to return a Location from Users as well as CommentCount and Score from Posts. We’ll go with User Id 822 since it’s currently August 2022.

We’ll want the actual execution plan so press CTRL-M or click the Include Action Execution Plan button. We’ll also want properties so we’ll press F4 if the Properties window isn’t already visible. Now we run this query:

SELECT u.Location, p.CommentCount, p.Score
FROM Users u
JOIN Posts p on u.Id = p.OwnerUserId
WHERE u.Id = 822;
GO

This query returned 72 rows of data from Users and Posts. If we have our SELECT highlighted and check the Optimization Level, we’ll see FULL.

Let’s try a simpler query that only retrieves the Location for Id 822:

SELECT Location 
FROM Users
WHERE Id = 822;
GO

We can check Properties again and see that this query had a trivial plan. All we have in our execution plan is a clustered index seek.

Potential issues

Trivial plans are short and sweet but that isn’t always a good thing. For example, SQL Server won’t bother giving you a heads up about missing indexes if a query has a trivial plan.

Let’s modify our query above and run:

SELECT Id 
FROM Users
WHERE Location = 'Campbell, CA';
GO

The query completed it under a second and had 30046 logical reads scanning the PK_Users_Id index.

We don’t see anything about a recommended index on Location which may give us a better plan. Let’s add that index, run the query again, and see what changed:

CREATE NONCLUSTERED INDEX [Location] ON [dbo].[Users]
(
	[Location]
);

Our query completed in under a second again but used the Location index. We went from 30046 logical reads to 3.

Reads aren’t the be all end all but this is a good example of improvements that you could be missing. No pun intended.

Thanks for reading!