Query taking long time

Hi Experts,

Need some help in optimizing the query. Please recommend some steps to optimzing the query. it is taking around 18 mins to execute. checked the waits stats , I see pageiolatch, io_completion etc...

Please let me know if you need any info from my end.

Thanks in advance.

sravan.pesession (225.5 kB)
avatar image By Maahi 1 asked Aug 21 at 07:52 PM
more ▼
(comments are locked)
10|10000 characters needed characters left

1 answer: sort voted first

You could use some better indexes on the tables that are being scanned on the right-end of the plan. Those Hash Matches are pulling a lot of rows in, and thinking that very few will match, which is causing some bad estimates to be used elsewhere in the plan. Look at the very left of the plan, and see that there are Index Scans and Table Scans that expect to be called once, that are actually called 20,000 times. This isn't good, and is because of your hash matches all the way down the right.

But also, you have lots of lookups. If you use 'included columns' in your non-clustered indexes, you can avoid those lookups and should see significant improvement.

avatar image By Rob Farley 196 answered Aug 26 at 01:05 AM
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 21 at 07:52 PM

Seen: 67 times

Last Updated: Aug 26 at 01:05 AM