Is there anything else I can do to make this query run faster and leaner?
The biggest gains would likely come from making the overall process simpler and more efficient. That would require a detailed analysis of the queries in the context of the business requirements they set out to meet, which we cannot know.
From the point of view of simply analysing the execution plans (albeit without the important performance information that would have been collected had the batch been run directly from Plan Explorer), I can say:
- Where possible and sensible, modify the existing indexes to avoid Key Lookups
- Avoid adding NOLOCK hints if that isolation level is not needed.
- Most tables should have a clustered index. Some of yours are currently heaps.
- Combined multiple temporary table updates if you can
The large Common Table Expression inserts (WITH CteResourceIds…) deserve special mention:
- Common Table Expressions are not tables. Each time you reference one, it will usually be fully evaluated each time. This is why the four String Splits in the query turn into six multi-statement function calls in the execution plan.
- It is often worthwhile turning the CTEs into temporary tables. This gives the optimizer cardinality information, and allows you to consider adding useful indexes to them.
- The query optimizer knows nothing about the result of a multi-statement table-valued function. If the result might affect the execution plan chosen, split the strings directly into temporary tables (not variables) instead.
- Consider adding OPTION (RECOMPILE) to this query (or queries, after refactoring). This can help simplify the plan based on the runtime values of parameters and variables.
- Adjust the indexing on Person to avoid the RID lookup. Consider adding a clustered index to this table.