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.

Hoyos

SQL Server will auto-parameterize some queries - I don't know the specifics, however, it could be that it's trying to optimize the above for any LIKE ... values and as such is making a bad plan choice for this particular search. – Valley

@marc_s, If you read the question, you can see that without any query hint, the query optimizer will pick the plan that requires 500x more IO. Perhaps it detected that my disk was idling and yearning for work? That could be a good reason. Anyway, assuming you did read the question again, you can see that I was asking for a way to help the query optimizer to pick the better plan without FORCESEEK. – Hoyos

I don't think SQL Server will look to the hardware and decide that a particular plan is good because the hardware is currently idle - plan creation is expensive wrt. resources - and plans are often created with reuse in mind. – Valley

@Will A, I was being sarcastic :P – Hoyos

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

Valley

Thanks, this works. Unfortunately, it is not very "ORM-friendly". – Hoyos

Well, with SQLAlchemy, there is no such thing as "not ORM-friendly". I have implemented this successfully. Thanks again for the answer. – Hoyos