Clustered index scan wrongly resorted to in execution plan

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Randolph Neall (randolphneall_at_veracitycomputing.com)
Date: 08/18/04 

Date: Wed, 18 Aug 2004 14:29:57 -0400

We are joining table A to table B using an indexed, two-column foreign key
of table B. If table A is restricted to a certain number of records, the
execution plan correctly utilizes the foreign key index on table B (index
seek). But if the restriction on table A is relaxed beyond a certain point,
then the execution plan does a clustered index scan instead, avoiding the
foreign key index on table B--and slowing the query five-fold.

In this rather surprising use of a clustered index scan, the criteria uses
the column functioning as the secondary part of table B's foreign key index,
not the primary portion, which in the execution plan is ignored.

It appears that SqlServer is making a bad decision for us. And we fear it
can only get worse as the database expands in size. What can we do?

Randy Neall