Tbl_Hurdle_Test Only has 25 000 rows why in the actual plan does the Table contain over 400 mil rows

sleon 2017-04-11 13:45:50

Hello Guys.

In the table Tbl_Hurdle_Test has only 25 000 rows but on executing the query according to actual execution plan there is over 400 mil rows in the table.

How can this be? I have checked statisic and statistics also indicate that there are only 25 000 rows in the table.

On estimated execution plan the table also only contains 25 000 rows.

Thanks for your help.

Regards, Leon.

Aaron Bertrand 2017-04-11 19:41:35
That number is an indictor of how many rows SQL Server needs to process, and doesn't necessarily mean that many rows can actually be returned. If you look closer at the expensive Table Scan operator, it shows that SQL Server thinks there are 25K rows in the table, but the optimizer has chosen a plan that, because of the nested loop, causes the scan to be executed 18,000 times. Curious to see what would happen if you added a clustered index to that heap, with a leading key of the measure ID.