How can I improve this stored performance?

PK 2017-04-25 20:01:17

How can I improve this stored performance?

One of issues is there are many warnings about 'operation caused residual io. the actual number of rows read differs from the number of rows returned'

Thank you!

Aaron Bertrand 2017-04-26 16:38:47
Every single estimate is wrong, which suggests that your statistics are out of date. It's also unclear from your anonymized code why you're doing so much I/O to table variables and using a cursor/loop – when you start stuffing thousands of rows into table variables and then using a cursor against them, things rarely end well. Could you try updating statistics on all underlying tables and adding OPTION (RECOMPILE) to the main queries that access base tables as a test to see if the estimates are better for different parameter values? As for the logic itself, well, with an anonymized plan there is only so much helpful advice you can get.