Need Help Tuning this Plan

Garry B 2016-06-17 15:59:16

This takes over 50 minutes to execute and one part is doing a RID lookup due to a table being a heap.

SQLkiwi 2016-06-20 13:27:33
You need to check your statistics are representative of the data. For example, the Clustered Index Seek on the Loan table estimates 1 row but produces 21,212 rows. This has a cascading effect on the rest of the plan. Update statistics as necessary, then reassess.

You can eliminate RID and Key Lookups in general but ensuring that the index touched immediately prior has all the columns needed, either in key columns or as an include. To determine which columns are missing, look at the Output List and any Predicate (not Seek Predicate) on the Lookup. Whether it is worth doing this depends on many things, such as the number of columns missing, the number of lookups performed, and whether the data typically comes from memory or disk. It is important to assess the impact (positive and negative_ of the indexing change on the workload as a whole. To take a simple example, the RID Lookup on IQLoanGroup that produces 9.5 million rows has no predicate, but LoanGroup in the Output List. The LoanGroup column could be added to the preceding index, Nk_ods_IQLoanGroup.

More generally, the plan shows Filters after a Sequence Project and Segment. This is commonly seen when a view contains a window function like ROW_NUMBER, and the query referencing the view has a condition that ought to be pushed past the window function, but isn't. You should try adding OPTION (RECOMPILE) to the query to see if this allows the optimizer to push the filter condition lower. This can have a dramatic impact in the right circumstances, but it depends on the detail of the query (which is truncated in the supplied file). My preference is to replace/supplement the view with an inline table-valued function with parameters that allow the filtering condition to be explicitly specified and positioned. For more details please see my Stack Overflow answer.