Estimated io cost of 0

Question

I have a query that performs really bad and when looking at the plan, I saw that it does a full table scan on a table and it executes this a number of times. This is causing the problem. If I force it to use an index instead, the performance is much better (bad plan = 18 seconds, good plan = 0.1 seconds).
The explain plan says that the estimated io cost of the full table scan is 0. I think that this is causing the cost of this plan to come in lower than the plan with the index hint.
The CPU cost is 46, so it does seem to calculate something with that.
I have attached a screenshot with the step that seems odd to me.
Am I in the right direction or is it normal to show an io cost of 0 at this point?

Answer 1

So it was as I suspected. The row goal raised its ugly head again. Don't get me wrong. There are situations where row goals make perfect sense. But the idea here is that the CI scan will quickly find a matching row, and therefore it does not have to run to the end, and therefore be cheaper than the index seek + key lookup. But if there is no matching row, guess what. It runs to the end and becomes very expensive. And why wouldn't run to the end? After, there is a reason you use [NOT] EXISTS.

Paul White has a series of blog posts about row goals, and I seem to recall that he described this use of row goals as an anti-pattern.

It is also interesting to observe that in the plan with the hint, the key lookup is now grossly over estimated in number of rows.

Answer 2

or is it normal to show an io cost of 0 at this point?

It reads 328 rows with each in size of 24 byte = actual data size = 8 KB; I am pretty sure SQL Server can hold this amount in buffer pool and so don't need to access the I/O subsystem.

Answer 3

The estimated I/O cost at 0 is suspicious, and I cannot give a good explanation for it. After all it estimates that it will read 42 million rows, even if it only expect to return 274 rows.

But the estimates are too far from the actual costs, so it does not seem to be case of a big mis-estimation.

I think that is much as I can say without seeing the query, and not seeing the full query plan. (And it would also be useful to see the plan with the hinted index.)

Answer 4

Hi @Mark Hellegers ，

Estimated I/O Cost and CPU Cost are just cost estimations as the names suggest. This estimated number just implies the amount of work CPU or I/O has to do to complete the task.

From this article, it says that operators that don’t do any I/O should of course be estimated to have a zero I/O cost. Operators such as Nested Loops, Assert, or Row Count Spool will always have an Estimated I/O Cost of zero.

All operators also have an Estimated I/O Cost property, that should be zero on operators that are not supposed to do I/O.
I think there is still doubt, high CPU cost but low IO cost.

-------------

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 5

Thanks for the plans. I don't really have that much useful to add.

In the bad plan, the subtree cost for the CI scan is a mere 0,21, which seems very optimistic. On the other hand, in the good plan, they Key Lookup alone has a very high cost, over 3. No wonder the optimizer does not like it!

This would be alright if the bad plan had some gross underestimate. OF for that matter, the good plan had had a gross overestimate. But the bad plan is decently on target, whereas the good plan has a gross underestimate of that Index Seek, so that Key Lookup has to work harder. And it still beats the CI scan. Something does not seem right in the costing to me...

But there is one thing we could try. What if you add this hint rather than forcing the index:

OPTION (USE HINT('DISABLE_OPTIMIZER_ROWGOAL'))