What performance improvements can be done on this

Sunil 2016-08-24 23:21:35

What performance improvements can be done on this?
Don't want to create any indexes. Can we improve more with out creating indexes?

Aaron Bertrand 2016-08-25 01:09:13
It seems your biggest problems in the plan, at least in terms of estimated costs, are lookups – which indicate index opportunities. If you're opposed to creating or updating indexes, there's not much else to be done, as you've anonymized the plan using an older version of Plan Explorer, which renders your entire query as Statement1. Could you possibly upload a non-anonymized plan or at least an anonymized plan from the most recent version, so we can investigate potential improvements in the query itself?
Vlady Oselsky 2016-09-01 14:02:15
Why would like to keep HEAPs? Creating non-clustered index on a HEAP is like ripping out pages from "Yellow Pages" and scattering them all over the floor and trying to find what you are looking for? Without having clustered key you have to create "covered" non-clustered index to avoid RID lookups. RID lookups are costly and will always produce performance problems. Lastly, I don't see any Duration and CPU cost in the plan which suggest you ran it in SSMS and use the plugin to open in Plan Explorer. If you rerun it in Plan Explorer you would get additional data that would be beneficial to fixing this.
SQLkiwi 2016-09-06 15:36:00
Without index changes, performance improvements might be obtained by:

  1. Adding an OPTION (RECOMPILE) query hint to get a plan optimized for the current value of any parameters or variables. This could also result in simplifications, for example by removing the need for a 'dynamic seek' as seen at the far right of the plan (constant scans and merge interval), and removal of startup Filters.
  2. Ensuring statistics are representative for all tables in the plan. This may affect the shape and content of the plan selected by the optimizer.
  3. Breaking the query up into smaller parts that are easier for the optimizer to assess and plan for individually. There are a large number of joins, so the chances are high that cardinality estimation errors will occur, even with updated statistics. Use one or more temporary tables (not temporary variables) to store parts of the query that execute efficiently. Natural places to break the query up are just before a sort, or just before a performance-affecting cardinality misestimation occurs. Done well, saving a small number of small intermediate results sets in one or more temporary tables can produce tremendous performance and stability improvements. The temporary tables can also be indexed without changing the schema of the main database, if this proves beneficial.