Avoid index scan in SQL Server 2008 R2

Question

I need some help here please.

I'm currently troubleshooting some dynamic SQL which is taking an age to return the results. I'm using SQL Server 2008 R2, build 10.50.4305

I've carried out a SQL trace so I can now see what's going on and basically the culprit is a clustered index scan.

The select contains:

WHERE 
    Name LIKE @Query + '%' ESCAPE '\' 
ORDER BY

This causes a clustered index scan.

If I change the select to

WHERE 
    Name LIKE 'value' + '%' ESCAPE '\' 
ORDER BY

I get an index seek and a key lookup.

I've looked as using the forceseek query hint but I get the error....

Msg 8622, Level 16, State 1, Line 6
Query processor could not produce a query plan because of the hints defined in this query. Resubmit the query without specifying any hints and without using SET FORCEPLAN.

Any help or advice on how to keep the parameter in the query and to avoid the clustered index scan would be much appreciated.

UPDATE (taken from comment by Shnugo)

I've added the query to the original question and the value of @Query is the same everytime whether the result is fast or slow

DECLARE @Query nvarchar(50)

SET @Query = 'EIKO'

SET NOCOUNT ON
SELECT TOP 5 pex.Ric.Name AS Id, pex.Ric.Name + '|' + pex.Ric.DisplayName AS Name FROM pex.Ric WITH (nolock) WHERE pex.Ric.Name LIKE @Query + '%' ESCAPE '\' ORDER BY PeId, Name

Answer 1

As pointed out in my comments, I assume, that some characters sitting in your dynamic SQL are interpreted as pattern. The usage of ESCAPE can be a hint, that you are aware of this and you inserted replace characters to mark these special characters as normal...

It looks, as if you were just looking for the same beginning... Try this

WHERE LEFT(Name,LEN(@Query))=@Query

This should cut the same count of characters from the left side of Name and compare it with your @Query variable.

This approach will not be quite as fast, as it is not sargable and will not be able to use the index too, but - if my assumption is correct - it should be faster, than a LIKE which is doing complex pattern checks for each row...

UPDATE (taken from comments below)

Could it be, that other heavy actions steal the server's ressources? Did you try without the ORDER BY?

I just tried it out and I can reproduce this. Searching for a literal means for the optimizer to know everything in advance: best prediction, best usage of statistics and index. Additionally your results could be misleading. If you execute this over and over, the results will be cached. With the identical query you might get very fast answers - but they are coming from the cache...

Parameter sniffing might be an issue too.

It could help to create the statement as string (with a literal) and use EXEC to run.