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
.
sys.dm_exec_query_profiles
, we'll re-cost it from actual costs vs. estimated). Stop using the estimated cost % as some absolute indicator of cost - it's relative and it's often out to lunch. – Aaron Bertrand Oct 26 '18 at 12:54