“Warnings: Operation caused residual I/O” versus key lookups

Question

I've seen this warning in SQL Server 2017 execution plans:

Warnings: Operation caused residual IO [sic]. The actual number of rows read was (3,321,318), but the number of rows returned was 40.

Here is a snippet from SQLSentry PlanExplorer:

In order to improve the code, I've added a non-clustered index, so SQL Server can get to the relevant rows. It works fine, but normally there would be too many (big) columns to include in the index. It looks like this:

If I only add the index, without include columns, it looks like this, if I force the use of the index:

Obviously, SQL Server thinks the key lookup is much more expensive than residual I/O. I have a test setup without much test data (yet), but when the code goes into production, it needs to work with much more data, so I'm fairly sure that some sort of NonClustered index is needed.

Are key lookups really that expensive, when you run on SSDs, that I have to create full-fat indexes (with a lot of include columns)?

---

Execution plan: https://www.brentozar.com/pastetheplan/?id=SJtiRte2X It is part of a long stored procedure. Look for IX_BatchNo_DeviceNo_CreatedUTC.

Answer 1

The cost model used by the optimizer is exactly that: a model. It produces generally good results over a wide range of workloads, on a wide range of database designs, on a wide range of hardware.

You should generally not assume that individual cost estimates will strongly correlate with runtime performance on a particular hardware configuration. The point of costing is to allow the optimizer to make an educated choice between candidate physical alternatives for the same logical operation.

When you really get into the details, a skilled database professional (with the time to spare on tuning an important query) can often do better. To that extent, you can think of the optimizer's plan selection as a good starting point. In most cases, that starting point will also be the ending point, since the solution found is good enough.

In my experience (and opinion) the SQL Server query optimizer costs lookups higher than I would prefer. This is largely a hangover from the days when random physical I/O was much more expensive compared to sequential access than is often the case today.

Still, lookups can be expensive even on SSDs, or ultimately even when reading exclusively from memory. Traversing b-tree structures is not for free. Obviously the cost mounts as you do more of them.

Included columns are great for read-heavy OLTP workloads, where the trade-off between index space usage and update cost versus runtime read performance makes sense. There is also a trade-off to consider around plan stability. A fully covering index avoids the question of when exactly the optimizer's cost model might transition from one alternative to the other.

Only you can decide if the trade-offs are worth it in your case. Test both alternatives on a representative data sample, and make an informed choice.

In a question comment you added:

Are you telling me that SQL Server does not know the cost of the residual IO?

No, the optimizer does consider the cost of residual I/O. Indeed, as far as the optimizer is concerned, non-SARGable predicates are evaluated in a separate Filter. This filter is pushed into the seek or scan as a residual during post-optimization rewrites.