Query extremely slow when joining 2 views

Question

I will preface this by saying i am not a DBA, just a dev that is trying to figure out performance problems with the following query we have.

 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

It takes about 1 minute to execute every time, we have double checked (we think) all required indexes, ran it through index tuner and still same issues. Below is the actual plan that it creates.

Any help would be appreciated, we are using sql server 2012 express

https://www.brentozar.com/pastetheplan/?id=HJgOtXHur

Answer 1

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);