Understanding below execution plan

Question

I have two tables tb1 and tb2, tb1 doesn't have any indexes on it. I populated tb1 with 1000000 rows and tb2 has 500 rows in it and with a clustered index on ID column.

To understand nested loop join, I have used below query:

SELECT * 
FROM tb1
INNER JOIN tb2 ON tb1.id=tb2.id 
OPTION(loop join);

I got below execution plan for this:

Tb1 which doesn't have index is scanned and cost is 2 %, whereas index is being used on tb2 and cost is 98%.

My question is:

1. How to understand estimated operator cost shown for above snip (138.236)

2. From the above snip of table scan (whose cost is 2%), number of executions are 24, will that mean sql reads rows in batches stored in memory, and for each row it did a seek operation from tbl2?

Can someone please explain to me the execution plan and also any pointers to know more about force scan, force index when I pressed F4 after clicking an operator.

Answer 1

1. How to understand estimated operator cost? Tb1 which don't have index is scanned and cost is 2 %, whereas index is being used on tb2 and cost is 98%.

The heap table is only fully scanned once, but the index seek is executed 1,000,000 times. The optimizer estimates that a million seeks in this case will represent 98.4% of the total cost of executing the query, whereas a single parallel scan of the heap table will represent 0.9% of the cost.

These are just estimates used for internal plan choice reasons; they do not generally reflect real-world performance on modern hardware, and are never anything more than an estimate - even in a post-execution ("actual") execution plan.

In Management Studio:

In SQL Sentry Plan Explorer:

2. From the above snip of table scan (whose cost is 2%),number of executions are 24,will that means sql read rows in batches and stored in memory and for each row it did a seek operation from tbl2.

No, it means 24 parallel threads co-operated to perform a single scan of the heap table. Each thread still reads a row at a time from the scan, performs a seek into the indexed table, then gets the next row from the scan, and so on until the task is complete.

Rows are not read in batches and stored in memory in this plan. SQL Server reports 24 scans because 24 threads each performed a partial scan of the table, resulting in one full scan overall.

3. Also any pointers to know more about force scan,force index when I pressed F4 after clicking an operator

The ForceScan, ForceSeek, and ForcedIndex properties are set to true if the query specifies a FORCESCAN, FORCESEEK, or INDEX hint - or if the query optimizer decides that a particular access strategy is required for correctness (for example, when checking foreign key constraints).