Estimated io cost of 0

Mark Hellegers 46 Reputation points
2021-11-30T09:59:02.587+00:00

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?

153674-afbeelding.png

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,314 questions
0 comments No comments
{count} votes

Accepted answer
  1. Erland Sommarskog 99,461 Reputation points MVP
    2021-12-02T22:39:34.573+00:00

    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.


4 additional answers

Sort by: Most helpful
  1. Olaf Helper 39,181 Reputation points
    2021-11-30T10:10:40.317+00:00

    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.


  2. Erland Sommarskog 99,461 Reputation points MVP
    2021-11-30T22:53:25.223+00:00

    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.)


  3. YufeiShao-msft 7,046 Reputation points
    2021-12-01T06:21:59.667+00:00

    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.

    0 comments No comments

  4. Erland Sommarskog 99,461 Reputation points MVP
    2021-12-01T22:13:18.207+00:00

    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'))