6

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:

Enter image description here

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:

Enter image description here

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

Enter image description here

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.

  • Question for you - Based on your last paragraph, why would the cost of a lookup be less based on the hardware? (Assumedly the same hardware that the non-clustered index will be running on) I'm unclear on that. – George.Palacios Oct 26 '18 at 12:40
  • 4
    It's estimated to be 76.9% of the cost of that plan. That doesn't mean it's expensive. Look at the I/O cost of 0.06 compared to your original plan with I/O cost over 10. I think you'll be better off, but you should test with actual plans against enough data that really simulates what production will be like (and if the query runs long enough for us collect data from 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
  • @AaronBertrand; the estimated cost of the key lookups is 31.0. Are you telling me that SQL Server does not know the cost of the residual IO? – Henrik Staun Poulsen Oct 26 '18 at 13:09
  • Where do you see 31.0? And do you mean 31.0 or 31.0%? – Aaron Bertrand Oct 26 '18 at 13:25
  • 1
    No, I'm saying that the costs you see are estimated costs and, as Paul explains below, don't necessarily reflect runtime performance. – Aaron Bertrand Oct 26 '18 at 13:29
15

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.

  • Thank you very much for your answer. I'll try to follow your advice about test data, so I can figure out what index I really need. It is good to know that you think lookups should cost less on SSDs. It bodes well for vNext. – Henrik Staun Poulsen Oct 29 '18 at 11:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.