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
Solutions
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.
Source
An example of implementing this is running
DBCC TRACEON(8690,-1)
(will be removed on instance restart)
Or better, running the query with OPTION(QUERYTRACEON 8690)
SELECT
OCCoverID as StaffID, FullName, SUM(OCWeight) AS Num
FROM vuOCStaffAbsentExpandedMini
INNER JOIN vuStaff ON OCCoverID = StaffID
WHERE
SADate = '2019-10-4'
AND MIDent = 506
AND SASupplyID IS NULL
AND CoverStaffSupplyID IS NULL
AND StaffMIDent <> 1
GROUP BY
OCCoverID, FullName
OPTION(QUERYTRACEON 8690);
VIEW
s are there? Are all the datatypes onJOIN
ed fields identical? Could you please provide the DDL for your views? – Vérace Oct 5 '19 at 0:00