How to minimize High Residual IO

mauripop 2017-12-09 01:21:23

I have this short straightforward query that is taking well over 30 secs to run. Plan Explorer tells me I have High Residual I/O. It's scanning the whole table instead of doing a seek. Indexes look fine. What can I do to optimize this one?

SDyckes2 2017-12-11 04:12:02
First, I would recommend updating the statistics on your indexes, the index used for the Index Scan, *TransT_dateTmernbr*, was last updated on 2017-07-01. The index that seems to be a better fit for your query should be TransactionsTMernbrIDTDate last had its statistics updated on 2017-11-15.

The statistics are the roadmap the optimizer uses when building a query plan. Outdated statistics are the equivalent to navigating in a new town with a 10-year old paper map. Current statistics are like using your map app on your smartphone to navigate to the same location. Which would you choose?

mauripop 2017-12-12 01:08:37
That was exactly right. I updated statistics for that table and problem solved, it's now using the right index and returning results instantly.

I've added a maintenance task to update statistics nightly. Thanks!
,That was exactly right. I updated statistics and problem solved.
I've scheduled a nightly task to update them now.

SDyckes2 2017-12-12 16:43:17
I am glad it worked out. I would recommend you have the statistics updated nightly for all your tables, then plan Index maintenance (Rebuild or Reorg) to run on a less frequent cadence (weekly, bi-weekly, or monthly).