Statistics On Temporary Tables
The attached TSQL.txt is the actual code being executed.
The attached zip file (ActualFromPEWithWithoutIndex_queryanalysis) contains the actual plans derived from PE before and after the index is applied on the temporary table
The Table Scan is on the inner side of a Nested Loops join. As the screenshot shows, the 35 million rows is the total over all iterations. The table is scanned 8,144 times. Adding the index provides a much more efficient access path, resulting in many fewer rows in total.
This bad estimate is also causing a suboptimal plan to be selected. In the After plan, you still have the same problem, but SQL doesn't have to scan #FillData each time to find the matching OrderId, resulting in a more efficient inefficient plan 😉
I'd advise changing the datatype on #WorkingData.PortId to match the source table, MA_Audit, and adding a n/c index on #WorkingData with AllocUserDef2 as the first key, and see what happens to the plan. Please post it if it changes things dramatically. Thanks.