Stats with full scan taking long time
By Ravi 1 asked Sep 10, 2015 at 05:58 AM
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.
By SDyckes2 96 answered Sep 10, 2015 at 06:36 PM