Nested Loop Output Estimate Low when expanding dataset I have a stored procedure that is taking some header and details information of a tactic, that goes on to expand the data from a date range (Start/End) to the day level. This is then used further on in the procedure to calculate some day-level weightings. As I've been performance tuning, I've noticed one specific issue that I can't seem to solve. 

If I have a "Tactic" that covers a year's worth of dates and the tactic item has 10,000 combinations that all need to expand out to the day level, I seem to get a low row estimate on the output of the nested loop. Investigating my plan, I believe it seems to be estimating the correct number of iterations at the nested loop, but at the output and subsequent inserts seem to be low. Is there something that I'm missing to gain better cardinality estimates?

Here's an extract of the behaviour:

DROP TABLE IF EXISTS dbo.Calendar
DROP TABLE IF EXISTS dbo.Tactic
DROP TABLE IF EXISTS dbo.TacticItems

CREATE TABLE dbo.Calendar (
    ddate   DATE NOT NULL
  , Day_Idx INT 
  , PRIMARY KEY (Day_Idx)
)

CREATE TABLE dbo.Tactic (
  TacticId      INT NOT NULL PRIMARY KEY
, From_Day_Idx  INT NOT NULL
, To_Day_idx    INT NOT NULL
)

CREATE TABLE dbo.TacticItems (
  TacticId      INT NOT NULL
, ItemKey_A     INT NOT NULL
, ItemKey_B     INT NOT NULL
, From_Day_Idx  INT NOT NULL
, To_Day_idx    INT NOT NULL
, PRIMARY KEY (TacticId, ItemKey_A, ItemKey_B)

)

/*
  Prime Dates For the Year
*/

INSERT INTO dbo.Calendar (ddate, Day_Idx)
SELECT  ddate = DATEADD(DAY, A.Increment, A.Base_Date)
      , Day_Idx = CONVERT(INT,CONVERT(CHAR(8),DATEADD(DAY, A.Increment, A.Base_Date),(112))) 
FROM    (
          SELECT TOP 366
                  Base_Date = CONVERT(DATE, '2019-12-31')
                , Increment = ROW_NUMBER() OVER (ORDER BY Object_Id)
          FROM    sys.objects
        ) A

INSERT INTO dbo.Tactic 
        (TacticId , From_Day_Idx, To_Day_idx)
VALUES  (29       , 20200101    , 20200630)

INSERT INTO dbo.TacticItems
            (
                TacticId
              , ItemKey_A
              , ItemKey_B
              , From_Day_Idx
              , To_Day_idx
            )
SELECT      1
          , A.ItemKey_A
          , B.ItemKey_B
          , 20200101
          , 20201231
FROM    (SELECT TOP 63 ItemKey_A = ROW_NUMBER() OVER (ORDER BY Object_Id) FROM sys.objects) A
CROSS JOIN (SELECT TOP 162 ItemKey_B = ROW_NUMBER() OVER (ORDER BY Object_Id) FROM sys.objects) B

DROP TABLE IF EXISTS #Results
SELECT      *
INTO        #Results
FROM        dbo.TacticItems    TI
INNER JOIN  dbo.Calendar       CAL
ON          CAL.Day_Idx BETWEEN TI.From_Day_Idx AND TI.To_Day_idx



Here is the plan that i'm getting:


And the nested loop input that seems to be correct:
That's the optimal plan for that query.  So you should consider storing the result.  One easy way is to use an Indexed View.

create view TacticItemsByDay
with schemabinding
as
SELECT  [TacticId], [ItemKey_A], [ItemKey_B], [From_Day_Idx], [To_Day_idx], [ddate], [Day_Idx] 
--INTO        #Results
FROM        dbo.TacticItems    TI
INNER JOIN  dbo.Calendar       CAL
ON          CAL.Day_Idx BETWEEN TI.From_Day_Idx AND TI.To_Day_idx

go

create unique clustered index ix_Results on TacticItemsByDay(TacticId, ItemKey_A, ItemKey_B, Day_Idx)