tsql - SQL Server does not choose to use index although everything seems to suggest it -

something wrong here , don't understand what. it's worth mention, there searched value not in table, existing value there no problem. though, why first query require clustered key search primary key not used in query, while second can run on index directly. forcing query use index with(index(indexname)) work, why optimizer not choose use itself.

the column piece_num not in other index , not primary key.

set statistics io on      declare  @vcheventnum   varchar(50)     set  @vcheventnum = '54235dds28kc1f5sjqmwz'      select top 1              fwt.weight,             fwt.test_result     fin_weight_tests fwt with(nolock)     fwt.piece_num @vcheventnum  + '%'       order fwt.dttm_insert desc       select top 1              fwt.weight,             fwt.test_result     fin_weight_tests fwt with(nolock)     fwt.piece_num '54235dds28kc1f5sjqmwz'  + '%'       order fwt.dttm_insert desc  set statistics io off 

i let both queries run in 1 batch:

io statistics report:

query 1: logical reads 16244910

query 2: logical reads 5

table 'fin_weight_tests'. scan count 1, logical reads 16244910, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.  table 'fin_weight_tests'. scan count 1, logical reads 5, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. 

the table has non-clustered index on piece_num including 3 other columns query.

here query execution plans(with little editing remove actual names):

i noticed convert_implicit, due conversion of varchar parameter nvarchar column. changing parameter type did not change behaviour of query.

why query parameter not use index while replacing parameter value does?

the first query going scan because using local variable. optimizer sees "anonymous" value , therefore cannot use statistics build query plan.

the second query seeks because literal value , sql can it's stats , knows better how many estimated rows find value.

if run first query follows imagine see use better plan:

declare  @vcheventnum   varchar(50) set  @vcheventnum = '54235dds28kc1f5sjqmwz'  select top 1          fwt.weight,         fwt.test_result fin_weight_tests fwt with(nolock) fwt.piece_num @vcheventnum  + '%' order fwt.dttm_insert desc option(recompile) 

i suggest using parameterized procedure run code ensure uses cached plan. using recompile hint has it's own drawbacks optimizer need rebuild plan every time runs. if run code avoid hint.

you can read local variables here:https://www.brentozar.com/archive/2014/06/tuning-stored-procedures-local-variables-problems/

wiki