>>ok, let me ask the question. Why are you setting the values in the script? Can't you set the values through parameters (instead of using variables)? SQL server is able to do parameter sniffing and use that to optimize the plan.
>
>What you saw was the auto generated result of my code. My code does pass those parameters as real SQL Client parameters.
Well, your code says this:
DECLARE @NoClient Int
DECLARE @NoStatus Int
DECLARE @NoStatus2 Int
DECLARE @NoStatus3 Int
DECLARE @NoStatus4 Int
SET @NoClient=3831141
SET @NoStatus=5
SET @NoStatus2=6
SET @NoStatus3=7
SET @NoStatus4=10
That is not the same as executing a parameterized query through sp_executesql(). That is because in the first, the optimizer cannot optimize for the parameter values while with sp_executesql, it can.
You also might execute it with the RECOMILE option. The optimizer will be forced to re-determine the execution plan for each call.
Can you rewrite the SQL to be executed through sp_executesql and see whether it resolves the issue?