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
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:

ClusteredScan

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:

IndexIntersect

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

June 25, 2014

Looking Back at 100 Illustrations

Filed under: Data Cartoons — Michael J. Swart @ 1:05 pm

So I recently took a look at the illustrations I have on this blog and I realize that I’ve got 100 illustrations. I’ve even built a page to show them off. Since 100 is a nice round number, I’m going to take this opportunity to look at some trends. I’ve grouped some of my illustrations into categories:

Movie Franchises

the Princess Bride:

Star Wars:

Star Trek:

Lord of the Rings:

Myself

Turns out I’m also a bit of a narcissist:

Women

A little over half of my illustrations are of people, and a smaller fraction are of fictional characters. But only a tiny fraction of those are women. I’m a bit worried about that. Maybe it says something about pop culture. Maybe it says something about me. I’ll have to give that some extra thought.

My Favorites:

This illustration has the right level of snark and it just makes me laugh. In my head I’ve titled this one “Grumpy Ted Codd” and I have this on a mug. It was one of my first illustrations I ever published and I’ve never been able to capture the same feeling of humour and relevance.

I was proud of this one because the likeness turned out. It was one of the first feelings I had that I was getting the hang of this.

Looking back on this now, their heads are too shiny. But this illustration was used with one of my most popular articles and I find myself looking for this article at least once a month. I hit the Browse By Illustration page and start looking for the mythbusters.

I did this one for a guest post on SQL Brit’s site. There were a lot of details in the spaceship and it’s a different kind of drawing than drawing faces. The pun works and I got to reference cheesy 80’s sci-fi… classic.

In General

It’s been a ton of fun. I don’t know what I have in store for the future but I do feel like a change of format is due.

As always, the comment form below is open. I’d like to hear what you think.

Powered by WordPress