Actually there is 1 query which confuses me , so I added cover index (non clustered index ( key column is ABC.number with included columns – all columns in SELECT area) and the performance improves a lot ( duration drop to 0 sec from 10 sec ) for particular parameter ( the parameter is ABC.Number )

But when it uses another parameter. the duration is still 10 secs ( it uses index scan instead of index seek)

I wonder why this happen as I expect it will behave the same for all parameters / value

This is the query :

SELECT ABC.XY_Create_Date XY_Create_Date,

ABC.ABC_Id ABC_Id,

dbo.rfn_IdToUserString(ABC.ABC_Id) Ticket_Number,

ABC.Name Name,

ABC.Status Status,

ABC.XY_Create_User XY_Create_User,

ABC.XY_Edit_User XY_Edit_User,

ABC.XY_Edit_Date XY_Edit_Date,

(cast(ABC.XY_Edit_Date as float) + 2) XY_Float_Edit_Date

FROM ABC ABC

WITH

(

READCOMMITTED

)

WHERE 1 = 1

AND 1 = 1

AND 1 = 1

AND 1 = 1

AND 1 = 1

AND 1 = 1

AND 1 = 1

AND 1 = 1

AND 1 = 1

AND 1 = 1

AND 1 = 1

AND (ABC.Number LIKE @P1

AND

/*CN*/

Memory increased a lot along with Slowness was reported so they are suppose to be correlated to each other