Big difference in estimated vs actual rows and seeking optimization help.

SQLCode 2015-09-11 05:10:28

I am certainly in need of optimization some code. It would be good if i get any suggestion from any one.

SDyckes2 2015-09-14 19:03:28
Without seeing a non-anonymized plan, it is hard to tell for sure what the cause may be on the numerous statements. One possibility would be out of date statistics. These would primarily be for the statements that have a higher Est Rows vs Actual Rows.

The other possibility I see is for most of the statements that have a lower Est Rows vs Actual Rows, especially when the Est Row count is 1. The Compiled Value in the parameters tab is 56761 for many of the statements, as shown below.

alt text

You can validate this by looking at your non-anonymized plan and checking the values. Recompiling the stored procedure(s) with the new value would fix this instance of the plan. The OPTION (RECOMPILE) may be worth considering to allow the plan to be optimized during each run for the various values passed to the code.

I hope that helps.