The query is taking 20 mintues to run. It's a view -which has a select statments

sqlmega 2014-07-11 15:50:36

Any help will be great.

/storage/attachments/949-Plan_Anonymized.pesession
Aaron Bertrand 2014-07-11 16:00:09
Tough to comment on the query since we can't see the query. Any chance you can post a non-anonymized version? Other than the query itself, one thing that stands out is estimated vs. actual is way off. This could be due to out-of-date statistics, but there are also a lot of implicit conversions – can you explain why several joins are made on columns whose data types don't match? Can this be corrected?
Kevin 2014-07-11 16:07:58
I want to second Aaron's comment about estimated vs actual plan. If you've never tried it before, rt-click on the plan diagram and toggle between the top two choices of Actual vs Estimated plan display.

You'll see HUGE differences in the two plans, centered around the Hash Match operation on the upper middle right. The Estimated plan sees very little costs throughout the plan, while the actual plan shows loads of costs, especially the 8Gb of data being churned by the Hash Match (about 28m rows which it iterates through twice).

So knowing nothing else, you definitely need some index and statistics refreshes.

But if you can share the actual code, that'd be even mo' better. =^)

Thanks,

-Kev

Edward Norris 2014-07-11 16:13:32
I agree,it appears to be a good size query. I'm impressed on all of the actual seeks and no scans. That's rare to see around here!

Also, it appears you are doing a "select distinct x from y order by z" which is consuming the 39%. My first question is…do you need to have the data sorted at this point? Would just doing a distinct work, and then sort at the end of the process? Also, without looking at the query, sometimes (very rarely) using a group by actually works faster than a distinct. However, this again is up to the data you are actually returning (and again, is very rare).

Try removing the order by and see what happens.

sqlmega 2014-07-12 07:37:11
link text[link text][2]

I have changed couple of indexes .now the query runs in 18 secs[link text][2]. can we improve this any thing more ? please find the actual plan attached

/storage/attachments/956-after_3.pesession
Aaron Bertrand 2014-07-15 11:53:28
Try updating stats on any index that shows a significant discrepancy between estimated and actual rows in the Top Operations tab, e.g. tblPartyRoleID.IX_tblPartyRoleID_RoleID. These missed estimates cascade throughout the plan. You could also try adding OPTION (MAXDOP 1) to the query, to see if parallelism is helping or hurting the execution time.
July 11, 2014
No Comments