Michael J. Swart

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
GO
 
DROP INDEX Person.Person.IX_Person_LastName_FirstName_MiddleName;
GO
 
CREATE INDEX [IX_Person_FirstName_LastName] ON [Person].[Person] 
( FirstName, LastName ) WITH (ONLINE=ON);
GO
 
CREATE INDEX [IX_Person_MiddleName] ON [Person].[Person] 
( MiddleName ) WITH (ONLINE=ON);
GO

The Trivial Plan

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

SET STATISTICS IO ON
 
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.

Careful

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!)