Search code examples
sql-serversql-server-2008-r2nested-loopstemp-tablessql-execution-plan

SQL Server uses Nested Loops because of bad estimated number of rows

I use SQL Server 2008 R2. I created a temp table and I fill my temp table with 1000 rows.

Create Table #Temp
(
  ID Int,
  res INT
)

Insert Into #Temp 
VALUES (10004, 2246), (10005, 2246), (10006, 2246), (10007, 2246),
       (10008, 2246), (10009, 2246), (10010, 2246), (10011, 2246),
       (10013, 2246), (10014, 2246), (10015, 2246), (10016, 2246),
       (10017, 2246), (10018, 2246), (10019, 2246), (10020, 2246),
       (10021, 2246), ................

I have another table named Item. It has about 30000 records.

I have an INNER JOIN between Item and my temp table.

Select 
    * 
From 
    Inventory.Item 
Inner Join
    #Temp On (#Temp.ID = item.MasterID And MRes = ExRestaurantID)

As you can see in three images below, SQL Server has created an execution plan for my query but in his plan, it has estimated that my Item table has just one row and because of that it has used Nested Loops join.

enter image description here

enter image description here

enter image description here

Dose anyone know why SQL Server has expected just one rows for Item table?

Solution

  • The estimates are exactly correct.

    Somewhat confusingly on the inside of a nested loops join the estimated number of rows is per execution but the actual number of rows is aggregated across all executions.

    It estimates 1 row per execution and 1,000 executions and you end up with an actual number of rows of 1,000.

    There is no discrepancy there.