Well, immediately I'm drawn to the Table Scan on Object11 that's feeding a
The Scan that's feeding the Aggregate might be worth considering for a ColumnStore Index if the data is mostly insert-only, or having an indexed view to have the aggregates pre-calculated.
There are also a bunch of TVFs that I can't see the detail of. And without the statements, which have been shrunk right down to simply "Statement1" and "Statement3", I can't suggest rewrites. But sorting out Object11 is definitely going to be useful. Start with creating the first of those indexes to see if the impact is significant.
By Rob Farley 196 answered Jan 11 at 02:02 AM