locked
Actual Rebinds - Sub Query RRS feed

  • 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
    • Moved by Tom Phillips Friday, April 17, 2009 1:48 PM TSQL Question
    Thursday, April 16, 2009 8:15 PM