What performance improvements can be done on this

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

avatar image By Sunil 1 asked Aug 24, 2016 at 11:21 PM
more ▼
(comments are locked)
avatar image Aaron Bertrand ♦ Aug 25, 2016 at 01:09 AM

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?

avatar image Vlady Oselsky Sep 01, 2016 at 02:02 PM

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.

10|10000 characters needed characters left

1 answer: sort voted first

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.

avatar image By SQLkiwi ♦ 6.6k answered Sep 06, 2016 at 03:36 PM
more ▼
(comments are locked)
10|10000 characters needed characters left
Your answer
toggle preview:

Up to 50 attachments (including images) can be used with a maximum of 209.7 MB each and 209.7 MB total.

Follow this question



asked: Aug 24, 2016 at 11:21 PM

Seen: 113 times

Last Updated: Sep 06, 2016 at 03:36 PM