Is there any obvious way to improve performance on this query?

BrainDedd 2016-07-12 12:59:24

Aaron Bertrand 2016-07-13 02:38:41
What is the actual performance problem you're observing? I see a key lookup that could be eliminated by covering those columns in Object12.Index23 (does that table really have 75+ columns?), and some under-estimates, but otherwise – with a duration of 416 milliseconds – how much faster do you think you can get this query to run? How often does it get run, and does it have different performance attributes when different parameters are used perhaps? Any chance you could supply a non-anonymized version (which makes it much easier to correlate things)?
BrainDedd 2016-07-13 04:43:55
Hi Aaron,

Thanks for having a look. I have posted a non-anonymized version. The query can be quick one moment but very slow the next and I am unsure exactly as to the cause.

It gets run very often (every few minutes and for each user.)

Aaron Bertrand 2016-07-14 15:49:50
Do you see different plans when it is slow vs. when it is fast? Are you using different parameters and/or different number of rows output? Are you seeing any blocking? What are the wait types? On quick glance I see that your runtime parameters and compiled parameters are different, suggesting that parameter sniffing is your core issue. You may get more predictable performance for the good parameters, but the same slower performance for the bad parameters, if you apply OPTION (RECOMPILE) to the query text. This is not free, of course, you can read more about it here and here. You might also consider using #temp tables instead of table variables, as the optimizer can do a little more with the former.
BrainDedd 2016-07-26 03:55:24
Managed to get it down locally to < 1 second thanks to your tips. Many thanks.