You have a performance spool that feeds your nested loops operator (non-apply)
...with very low estimates.
Now, for each value in the top(outer) input, the table spool will produce a rebind. No rewinds are possible for performance spools on non apply nested loop joins.
...The lazy table spool is populated once during the first loop
iteration. The spool rewinds its contents for each subsequent
iteration of the join. With nested loops join, the inner side of the
join is a static set of rows because the join predicate is on the join
itself. The static inner-side row set can therefore be cached and
reused multiple times via the spool. A nested loops join performance
spool never rebinds....
Source: Paul White on Nested Loops Joins and Performance Spool
The low estimates result into the actual situation below:
You can validate the high rowcount of the spool output by multiplying both inputs (of the compute scalar & the stream aggregate).
515 728 (outer input)
* 168 (inner input) =
86 642 304
The first thing to fix would be fixing why the estimates are so low.
You could try updating the statistics of the table referenced in the view.
But it appears that you are doing a
LIKE comparison between
dbo.TblOnCall and the tables feeding the spool operator.
That can also be seen on the NL operator that gets the data from the spool.
EXPR 1137 = the outer input,
UNION = the inner input).
For a longterm solution,
I would fix this
LIKE comparison and any other parts that will impact your estimations gravely.
We would have to see the views to give the most correct query recommendations.
For a temporary quick fix, you can disable the spool with a trace flag or a query hint.
Performance spools can be disabled with lightly-documented trace flag
8690, or the documented query hint NO_PERFORMANCE_SPOOL on SQL Server
2016 or later.
An example of implementing this is running
DBCC TRACEON(8690,-1) (will be removed on instance restart)
Or better, running the query with
OCCoverID as StaffID, FullName, SUM(OCWeight) AS Num
INNER JOIN vuStaff ON OCCoverID = StaffID
SADate = '2019-10-4'
AND MIDent = 506
AND SASupplyID IS NULL
AND CoverStaffSupplyID IS NULL
AND StaffMIDent <> 1