What indexes should I add to odsstaging to improve the performance of this query

NickF 2018-02-19 14:31:22

SDyckes2 2018-02-19 20:58:33

In the title of the post, you are asking about adding indexes to odsStaging. I assume that is a table, but, you posted an anonymized plan which does not have table names. The only index or table seek or scan I found was on Object3.Index1, which was an index scan. You may be able to create a non-clustered index on that table on the Column20 data to get better performance on that Scan. Though the Estimated plan shows only 1 row being returned by that scan, so it would appear an index would not help on that object.

Looking at your plan, I see two Table-Valued Functions (TVF) that are the bulk of the estimated CPU cost. One function returns 648,000 records each of the 31 times it is being called. The other function is returning more than 59 million records for each of its 13 times being called.

If you are looking to improve the query performance, I strongly recommend analyzing what the TVFs are doing and if there is a better method.