Attack Key Lookups first?
Anyone have any suggestions after looking at this plan?
BTW – there are two cursors in this procedure that have been hard coded to skip just so I could get a plan. I'm trying to figure out how to eliminate those also.
This process runs a massive query plan once for each row of the nested cursors. This is not a recipe for good performance. You are almost certainly correct in thinking that eliminating the lookups will not affect performance much.
If this were my query, I would probably start again, starting from the business requirements. I would write the process without cursors, and with the simplest (and most maintainable) set of SQL statements available. At least to begin with, this would probably involve multiple steps, of a manageable size, with intermediate results (between steps) held in temporary tables. I would also typically simplify the logic to the bare minimum to begin with, adding complexity (exceptions, edge cases etc.) step by step.
During this process, you should be able to identify exactly where the performance issues arise, and improve the schema or indexing appropriately. I would also look to reduce or eliminate the use of non-inline functions where practical. I would definitely look to avoid such large monolithic queries, even in the finished product – they're just too hard to maintain, and too often tend to have inconsistent performance characteristics.
I do apologise if you were looking for more specific advice, but with the time and information I have available, the above is about the best I can offer.