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