Cluster index scan with different cost

sakuraime 2,316 Reputation points
2020-10-15T15:20:36.487+00:00

from the below, the cluster index scan was on the same table, but why one cost is 99 and another one is 0 ?

32714-clusterindex.jpg

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,292 questions
{count} votes

3 answers

Sort by: Most helpful
  1. Erland Sommarskog 99,071 Reputation points MVP
    2020-10-15T21:34:57.663+00:00

    If you want a good factual answer, you need to post the full plan, preferably in XML format.

    But I would guess that the optimizer estimates that the Nested Loop join above filters out most of the rows from the spool operator, so that there will be fewer scans. Or the operator to the left that you did not include is a hash/merge join, so that scan is only done once. Or that table is simply a lot smaller.

    1 person found this answer helpful.

  2. Olaf Helper 38,941 Reputation points
    2020-10-16T05:35:06.663+00:00

    but why one cost is 99 and another one is 0 ?

    May be because the one table is large and the other very small? How many rows do each table have?


  3. CathyJi-MSFT 21,116 Reputation points Microsoft Vendor
    2020-10-16T07:02:22.803+00:00

    Hi @sakuraime ,

    a nested loops join compares each row from one table (known as the outer table) to each row from the other table (known as the inner table) looking for rows that satisfy the join predicate. It will scan the table many times, this may cause many cost. Please refer to Nested Loops Join.

    Best regards,
    Cathy


    If the response is helpful, please click "Accept Answer" and upvote it.
    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.