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.
By Maahi 1 asked Aug 21, 2017 at 07:52 PM
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.
By Rob Farley 196 answered Aug 26, 2017 at 01:05 AM