Получите, чтобы SQL Server использовал поиск по индексу + поиск ключа вместо сканирования кластерного индекса без WITH (FORCESEEK)

Question

Версия: SQL Server 2008 R2

База данных: AdventureWorks 2008R2 от http://msftdbprodsamples.codeplex.com/releases/view/55926

Запрос:

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

Без каких-либо подсказок в запросе SQL Server будет использовать сканирование кластерных индексов, которое требует интенсивного ввода-вывода:

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 (FORCESEEK) подсказка для запроса, SQL Server выберет поиск по индексу + поиск по ключу, который завершается быстрее и в 500 раз добрее 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.

У меня вопрос, есть ли способ заставить SQL Server использовать лучший план без подсказок? Возможно, добавление индексов? Или изменение параметров конфигурации? Или оптимизатор запросов SQL Server не имеет понятия?

Вот драгоценный камень от http://msdn.microsoft.com/en-us/library/bb510478.aspx:

Правила оптимизатора запросов и плохая оценка количества элементов также могут привести к тому, что оптимизатор выполнит операцию сканирования таблицы или индекса вместо поиска по индексу, когда запрос использует IN или LIKE в качестве предикатов поиска.

Answer 1

Вот версия, которая показывает "приличные" цифры IO, не прибегая к FORCESEEK. Интересно, что этот ужасно выглядящий запрос работает лучше.

ОТКАЗ ОТ ОТВЕТСТВЕННОСТИ: Это может работать лучше для "Max%" и "122%", но как это будет работать, если план повторно используется для "M%" и "%", это другой вопрос...

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