Query extremely slow when joining 2 views
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 &amp; 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 &lt;&gt; 1
    GROUP BY
        OCCoverID, FullName
OPTION(QUERYTRACEON 8690);