Function call as INNER JOIN causes slow RID lookup

Always hard to give any decent advice with an anonymized plan...

But, remove the table valued function. A)dump the results of that into a temp table and use that in the query if using 100% of its output B) find another way to to its activity if not.

The problem is TVF always estimate at one row, here it is returning 564 rows.

Table2 is also giving a relatively poor estimate, the error of which is being magnified up the tree.

HTH

Although the RID Lookup operator has a cost of 0.1%, the number of iterations (1,185,988 times lookups)is very high. The lookup is Column 18 of Table 14. If the Table14.Index9 is altered to include the Column 18, there should be good improvement.

Thanks for all your help so far. I've looked at the suggestions and eliminated the columns and tables mentioned from the query by removing them completely and the query still runs very slow. As an exercise, I have whittled it down to the bare minimum so that it still runs very slow, but with only the two JOINs that cause it to take long. Without either of these (even with everything else included), it runs quickly.

The non-anonymised query analysis (see here) shows an horrendous number of rows being outputted, but it takes a long time in SSMS before it even starts outputting the rows to the 'Results' window. What I found is that the function call simply returns a single-column list and the purpose of the JOIN is to filter the results on that list. It occurred to me that this should actually go in the WHERE clause.

So, the resultant query is

 SELECT * FROM F_V_IrZeroDelta (NOLOCK) IRD
     INNER JOIN dbo.D_Trade (NOLOCK) T ON T.Trade_PK = IRD.Trade_FK_LK
 WHERE T.TradeTypeName_LK IN (SELECT TradeTypeName_LK FROM feeds.fn_GetTradeTypes(NULL))

This is much quicker although not perfect - it still takes several minutes, but that's better than over an hour.

1. The table variable (@Jobs) is one of the main culprit limiting the optimizer to generate an inefficient plan. The estimated number of executions is 1 versus actual executions 7 on the F_IrZeroDelta Clustered Index Seek in conjunction with estimated rows 331K versus 9679K indicates that seek was inefficient and sql server could have done something better. The Threads 1 to 4 on the F_IrZeroDelta Clustered Index as shown in the Plan Tree also indicates the same wherein the load was not balanced and Thread 0 had to wait unnecessarily for all the 4 threads to complete successfully. This could have increased the CXPACKET wait.

2.The Statistics of the table is "F_V_IrZeroDelta" is either outdated or is based on a small sampling