Actual Rebinds - Sub Query

Question

Hello All,

Need your expert advice...

I have a stored proc which basically works like a search function...

It is a dynamically built query and the problem with this one we encountered
was recompilations. From the profiler I can see the CPU taking a hit. It does
not recompile the whole stored procedure but only the SQL Statement. The
profiler says that the recompiles are due to "1 - Schema Changed" while there
are no changes. I am assuming that even change in statistical data might
trigger this recompilation as I do see Autostats getting fired and stats
updating on those particular indexes.

So now we changed the whole stored procedure and removed sp_executesql and
its a static query. But it seems the cost of this new stored proc is even
more higher. It uses a subquery which filters the value using a like clause.

Another reason for us to change the stored proc from dynamic
to static was that the number of parameters changed. This could be a
possibility of why optimizer would take this as a change and recompile the
statement.

The cost of the full query shows as 4.34. CPU cost 0.0000001 and the IO cost
is 0. As I understand  understand the cost is mostly related to the CPU and
the IO. So things dont add up here. Secondly the operator within the query
with the highest cost shows an index seek with cost 4.34 . On the index seek
the estimated operator cost and estimated subtree cost is 4.34,  CPU cost is
0.0001583 and the IO cost is 003125. So again not sure where the whole
operator cost adds upto 4.34.

Another part, the Estimated Number of rows 1 and Actual number of rows is
1240. Possibility of the optimizer not generating a good execution plan. The
Indexes are rebuilt everyday and another job does an update stats with full
scan every 3 hours. From the execution plan the StatementOptmLevel="FULL"
StatementOptmEarlyAbortReason="GoodEnoughPlanFound".

The only thing which I feel might contribute to the cost could be the Actual
Rebinds which shows up on the previous operator to Index seek which is the
filter operator. Actual Rebinds is 133678 and Actual Rewinds is 0

Could it be the number of times this operator is executed ? Any pointers on
where I should be looking further or how to optimize this query will be
helpful. Thanks

Regards
Shams