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:
- How to understand estimated operator cost shown for above snip (138.236)
- 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.