How can I tune this query

Dave55 2014-01-15 21:36:42

Aaron Bertrand 2014-01-15 22:44:39
Any chance you can share a non-anonymized plan? It's tough to tune the query when the query isn't available. 🙂
SQLkiwi 2014-01-15 23:31:28
Even if a non-anonymized plan is not an option, a post-execution plan would be nice. Even better if the query/procedure was run and details collected directly from Plan Explorer.
SQLkiwi 2014-01-15 23:37:51
Based solely on the very limited information based on the original anonymized, pre-execution plans provided:

  1. Look to avoid Key and RID Lookups (where sensible) by providing covering indexes.
  2. Check lookup output columns and predicates to see which columns are missing.
  3. Avoid large scans, especially table scans on the inner side of a nested loop join.
  4. Consider alternatives to recursive CTEs.
Dave55 2014-01-17 23:40:47
Let me know if this is better.link text

Also it PE says: A query plan was not collected at the time of execution. Estimated plan generation is only available for non-dynamic root statements…

Is that because I ran a sp? Is there a way to generate the plan?

Aaron Bertrand 2014-01-20 22:43:53
Thanks, looking now, just wanted to answer your question in the meantime. Your batch has multiple statements, and each statement (that can) generates its own plan. You need to highlight relevant statements in the statements tree in order to see the plan for that statement. By default, we highlight the very first statement, which is a USE database command. We don't show a plan in this case because a USE command does not generate a plan. You need to highlight statements further down the tree to dig into more details about that statement.