2

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

  • 1
    estimated vs actual rows for the table spool is substantial - 1589 vs 86 million. Check your views. – Kin Shah Oct 4 at 20:51
  • 1
    How many levels of VIEWs are there? Are all the datatypes on JOINed fields identical? Could you please provide the DDL for your views? – Vérace Oct 5 at 0:00
1

You have a performance spool that feeds your nested loops operator (non-apply)

...with very low estimates.

enter image description here

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:

enter image description here

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.

enter image description here

That can also be seen on the NL operator that gets the data from the spool.

enter image description here

(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);
  • seems like the hint sort of fixed the query to be better performant but ultimately the updating of the db stats fixed it all, thank you – Zoinky Oct 7 at 15:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.