SQL Server 2005 cached an execution plan that could never work

Question

We have a view that is used to lookup a record in a table by clustered index. The view also has a couple of subqueries in the select statement that lookup data in two large tables, also by clustered index.

To hugely simplify it would be something like this:

SELECT a,
(SELECT b FROM tableB where tableB.a=tableA.a) as b
(SELECT c FROM tableC where tableC.a=tableA.a) as c
FROM tableA

Most lookups to [tableB] correctly use a non-clustered index on [tableB] and work very efficiently. However, very occasionally SQL Server, in generating an execution plan, has instead used an index on [tableB] that doesn't contain the value being passed through. So, following the example above, although an index of column [a] exists on tableB, the plan instead does a scan of a clustered index that has column [z]. Using SQL's own language the plan's "predicate is not relevant to the object". I can't see why this would ever be practical. As a result, when SQL does this, it has to scan every record in the index, because it would never exist, taking up to 30 seconds. It just seems plain wrong, always.

Has any one seen this before, where an execution plan does something that looks like it could never be right? I am going to rewrite the query anyway, so my concern is less about the structure of the query, but more as to why SQL would ever get it that wrong.

I know sometimes SQL Server can choose a plan that worked once and it can become inefficient as the dataset changes but in this case it could never work.

Further information

• [tableB] has 4 million records, and most values for [a] are null
• I'm unable now to get hold of the initial query that generated the plan
• These queries are run through Coldfusion but at this time I'm interested in anyone having seen this independently in SQL Server

Answer 1

It just seems plain wrong, always.

You might be interested in the First Rule of Programming.

So, following the example above, although an index of column [a] exists on tableB, the plan instead does a scan of a clustered index that has column [z].

A clustered index always includes all rows. It might be ordered by z, but it will still contain all other columns at the leaf level.

The reason SQL Server sometimes prefers a clustered scan over an index seek is this. When you do an index seek, you have to follow it up with a bookmark lookup to the clustered index to retrieve columns that are not in the index.

When you do a clustered index scan, you by definition find all columns. That means no bookmark lookup is required.

When SQL Server expects many rows, it tries to avoid the bookmark lookups. This is a time-tested choice. Nonclustered index seeks are routinely beaten by clustered index scans.

You can test this for your case by forcing either with the with (index(IX_YourIndex)) query hint.