Get SQL Server to use index seek + key lookup instead of clustered index scan, without WITH (FORCESEEK)

Question

Version: SQL Server 2008 R2

Database: AdventureWorks 2008R2 from http://msftdbprodsamples.codeplex.com/releases/view/55926

Query:

SELECT TOP 10
    *
FROM
    Person.Person --WITH (FORCESEEK)
WHERE
    LastName like 'Max%'
    OR EXISTS (
        SELECT
            1
        FROM
            Person.PersonPhone
        WHERE
            Person.PersonPhone.BusinessEntityID = Person.Person.BusinessEntityID
            AND Person.PersonPhone.PhoneNumber LIKE '122%'
    )
ORDER BY Person.Person.BusinessEntityID DESC

Without any query hint, SQL Server will use clustered index scan, which is IO intensive:

Table 'PersonPhone'. Scan count 14170, logical reads 28446, physical reads 15, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Person'. Scan count 1, logical reads 2844, physical reads 3, read-ahead reads 3215, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

With the WITH (FORCESEEK) query hint, SQL Server will opt for index seek + key lookup, which completes faster and is 500x kinder to the IO:

Table 'Person'. Scan count 1, logical reads 59, physical reads 22, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'PersonPhone'. Scan count 1, logical reads 2, physical reads 2, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

My question is, is there any way to get SQL Server to use the better plan without any query hint? Adding indexes perhaps? Or changing config parameters? Or is SQL Server's query optimizer that clueless?

Here's a gem from http://msdn.microsoft.com/en-us/library/bb510478.aspx:

Query optimizer rules and poor cardinality estimation can also cause the optimizer to perform a table or index scan operation rather than an index seek when a query uses IN or LIKE as search predicates.

Answer 1

Here's a version that exhibits 'decent' IO figures without resorting to the FORCESEEK. It's interesting that this horrible looking query performs 'better'.

DISCLAIMER: This may perform better for 'Max%' and '122%', but how it'll perform if the plan is reused for 'M%' and '%' is another matter...

SELECT TOP 10 *
FROM Person.Person P
INNER JOIN (
SELECT BusinessEntityID
FROM    Person.Person --WITH (FORCESEEK)
WHERE    LastName like 'Max%'    

UNION

SELECT BusinessEntityID
FROM    Person.Person --WITH (FORCESEEK)
WHERE    EXISTS (        SELECT            *        FROM        
    Person.PersonPhone        WHERE            Person.PersonPhone.BusinessEntityID = Person.Person.BusinessEntityID            AND Person.PersonPhone.PhoneNumber LIKE '122%'    )
) NOTNICE
ON NOTNICE.BusinessEntityID = P.BusinessEntityID
ORDER BY P.BusinessEntityID DESC