Slow Query should I create the recommended index

Stats with full scan taking long time

avatar image By Ravi 1 asked Sep 10, 2015 at 05:58 AM
more ▼
(comments are locked)
10|10000 characters needed characters left

1 answer: sort voted first

The index looks like a good choice to add as a non-clustered index, as is shown by the image below of Output list for the Clustered Index Scan of Object12.Index4, in addition, column10 is used in a Group By in the CTE and an Order By in the end result query.

But also note the Actual and Estimated rows I have highlighted, and the large difference for Object12. I would recommend updating the statistics on Object12, Object8, Object 6, and Object4, then generating a new plan for analysis. Updated statistics may change the plan with the new information available for the optimizer, but it will probably not change the clustered index scan. The difference in the Actual vs Estimated on these Objects is very severe; these underestimates are also causing TempDB spills on the Sort Operators.

I would then test the new index recommendation, which should reduce the time to retrieve data from Object12 by performing an Index Scan or Seek.

alt text

avatar image By SDyckes2 81 answered Sep 10, 2015 at 06: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.