Geek asks and answers

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

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 tooltips, SQL Server will use clustered index scanning, which will be intensive nested:

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 a WITH (FORCESEEK)query prompt, SQL Server will select a search by index + key, which will complete faster and 500 times more than 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 a way to get SQL Server to use a better plan without prompting? Perhaps adding indexes? Or change the configuration settings? Or a SQL Server query optimizer that doesn't matter?

http://msdn.microsoft.com/en-us/library/bb510478.aspx:

, , IN LIKE .

+3
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
+3

More articles:


All Articles