Michael J. Swart

June 27, 2014

Trivia about Trivial Plans

Filed under: SQLServerPedia Syndication — Michael J. Swart @ 12:43 pm

Takeaway: I found an example of a query plan which performs better than the “trivial” query plan.

This post is trivia in that it won’t help you do your job as a developer or DBA. But it’s interesting anyway. It offers a look into an interesting part of SQL Server’s query optimizer.

The Setup

I use the 2012 AdventureWorks database and I mess around with the indexes. It’s a setup that Kendra Little developed in order to demonstrate index intersection.

use AdventureWorks2012
DROP INDEX Person.Person.IX_Person_LastName_FirstName_MiddleName;
CREATE INDEX [IX_Person_FirstName_LastName] ON [Person].[Person] 
( FirstName, LastName ) WITH (ONLINE=ON);
CREATE INDEX [IX_Person_MiddleName] ON [Person].[Person] 
( MiddleName ) WITH (ONLINE=ON);

The Trivial Plan

In management studio, include the actual query plan and run this query:

SELECT FirstName, MiddleName, LastName
FROM Person.Person
-- 19972 rows returned
-- 1 scan, 3820 logical reads
-- optimization level: TRIVIAL
-- estimated cost: 2.84673

With such a simple query – one against a single table with no filters – SQL Server will choose to scan the narrowest covering index and it won’t bother optimizing the plan any further. That’s what it means to say the optimization level is TRIVIAL.

For this query, the only index that contains all three columns is the clustered one. So it seems there’s no alternative but to scan it. That sounds reasonable right? That’s what we see in the query plan, it looks looks like this:


But notice that SQL Server is doing a lot of reading with this plan choice. The table Person.Person has a column called Demographics. This xml field makes the table very wide, so wide that a typical page in Person.Person can only fit about 5 or 6 rows on average.

The Better-Than-Trivial Plan

Now look at this query:

SELECT FirstName, MiddleName, LastName
FROM Person.Person
WHERE FirstName LIKE '%'
-- 19972 rows returned
-- 2 scans, 139 logical reads
-- optimization level: FULL
-- estimated cost: 1.46198

The filter is put in place to have no logical effect.  It complicates things just enough so that SQL Server won’t use a trivial plan. SQL Server fully optimizes the query and the query plan now looks like this:


Notice that the plan has scans on two indexes nonclustered indexes and a hash join. SQL Server figures (correctly) that scans of two narrow indexes plus a hash join are still cheaper than the single scan of the fat clustered index.


I don’t think I need to say this, but I do not recommend adding WHERE column like '%' anywhere except maybe in contrived examples for demo purposes.

(MJS — Enjoy the summer, See you in September!)


  1. Nice trick! Is the predicate being deleted statically? If not, we can maybe find a predicate that can be removed altogether during compilation.

    Comment by tobi — June 28, 2014 @ 7:52 am

  2. Hey tobi,

    No, I don’t believe the predicate is being deleted statically.

    In theory it could because every non-null value will satisfy [value] like '%', and FirstName is not nullable. But SQL Server doesn’t recognize this kind of shortcut.

    But there are some shortcuts it recognizes… for example FirstName IS NOT NULL is removed altogether during query compilation like you were wondering. But the interesting thing is that this step is called Simplification. And the Simplification step comes before the Trivial Plan step. (See Query Optimizer Deep Dive).

    To sum up:

    SELECT FirstName, MiddleName, LastName
    FROM Person.Person

    is compiled into the same slower TRIVIAL plan described in the post.

    Comment by Michael J. Swart — July 2, 2014 @ 2:06 pm

RSS feed for comments on this post. TrackBack URL

Leave a comment

Powered by WordPress