>This indeed is an optimizer issue. The optimizer is not able to do parameter sniffing in the example below. Therefore it might use a non optimal execution plan as the execution plans are cached from one execution to another.
>
>Depending on the selectivity of the parameters that execution plan might not be optimal. If you really need parameterization, you'll have to force a recompile of the execution plan. If you're using SPs, the WITH RECOMPILE option is helpfull.
>
>With SPT, you're either 1. deemed to find a way to force a recompile or 2. to restructure the SQL statement to be less phrone to this and 3. make sure you have the right indexes.
>
>1. Don't use parameterisation for at least one of the parameters or make sure that SQL string is slightly different as SQL server looks at the string and looks up whether that exact same statment has been executed before and uses that executionplan. If SQL server cannot find the exact SQL statement, it will force a new execution plan.
>
>2. Some statements can be better optimized. For example, using ORs often can be written better using unions.
>

SELECT Client.Numero FROM Client WHERE Client.Name LIKE @Name AND Client.ID=@ID
>UNION
>SELECT Client.Numero FROM Client WHERE Client.Name2 LIKE @Name AND Client.ID=@ID
>

>
>3. In the case of the query above, the query would greatly benefit on a compound index on ID, Name, Numero, so SQL server can scan a small range of index nodes and not even touch the table (full index coverage). Smartly choosen compound indexes are a great way to increase performance.
>
>On the whole I don't think the clustered index is going to make a significant case here. Their role is going to be minimized in the future with the rise of SSD harddisk... There is less need to keep freuquently queried records physically together.

Thanks

I have isolated a few issues in regards to this.

1. Where not necessary, I have removed the TOP clause in my SQL. I have found that SQL Server will use a non optimize route if I have a TOP clause in my SQL. From the SSMS interface, if I really need that, I am using OPTION (RECOMPILE), which resolves the issue.

2. The use of a non optimize route is related to character fields being used in a SQL. Thus, in such circumstance, I am now forcing the data type to be passed in the SQLParameter() object. The result turned out to be instantly.