quote:

---

Originally posted by _jesse

This seems to be a classic parameter-sniffing problem -- except that I can't seem to force the query to run with the correct plan. The proc below tries to just select using LIKE. When I use a literal string predicate, I get a nice, fast index seek. All of the other variants do an index scan -- of the wrong index.

What else can I do to force SQL Server to use the correct index?


DROP PROCEDURE test_like
GO

CREATE PROCEDURE test_like
@searchText nvarchar(64)
AS

-- Straight literal string search. Runs quickly with index seek on companies.searchbrand

SELECT companyId
FROM companies
WHERE searchbrand LIKE 'adv%'


-- Each of the queries below does an index scan on the primary key index on companies.companyId
-- instead of doing a seek on the correct index.

-- Search for proc parameter

SELECT companyId
FROM companies
WHERE searchbrand LIKE @searchText


-- Search for proc parameter, with hint
SELECT companyId
FROM companies
WHERE searchbrand LIKE @searchText
OPTION ( OPTIMIZE FOR (@searchText = 'adv%') )

-- Local variable
DECLARE @localText nvarchar(64)
SET @localText = @searchText

SELECT companyId
FROM companies
WHERE searchbrand LIKE @localText

GO

EXEC test_like 'adv%'

---