Sql-server – SQL Server expensive nested loops join and lazy table spool

execution-planperformancequery-performancesql serversql-server-2016t-sql

I am trying to tune the below query which takes 15-16 seconds no matter what value is passed in as a parameter, the query is:

select distinct d.documentpath as path, d.documentname as name, d.datecreated as created, pc.DateProcessed
from datagatheringruntime dgr 
 inner join processentitymapping pem on pem.entityid = dgr.entityid 
 inner join document d on d.entityid = pem.entityid or d.unitofworkid = pem.processid
 left join PendingCorrespondence pc on pc.PendingCorrespondenceId = d.PendingCorrespondenceId
 where rootid = @P0 and dgr.name in('cust_pn', 'case_pn')
OPTION(RECOMPILE)

I have updated the statistics for all the tables touched by the query (excluding the DataGatheringRuntime table which is quite big at ~100GB) and have tried re-factoring the query using a CTE but get the same execution plan and need some assistance.

The actual execution plan can be found here:

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

It's clear from the execution plan that the problem lies with the outer input on the nested loop join specifically with the lazy table spool following the scan of the non-clustered IX_Camunda_1 index on the Document table but I have no idea how to tackle that issue and would appreciate any guidance.

Best Answer

I would try removing the OR clause in the join between document and processingentitymapping

You could do that with UNION

SELECT distinct d.documentpath as path, d.documentname as name, d.datecreated as created, pc.DateProcessed
FROM datagatheringruntime dgr 
INNER JOIN processentitymapping pem on pem.entityid = dgr.entityid 
INNER JOIN document d on d.entityid = pem.entityid 
LEFT JOIN PendingCorrespondence pc on pc.PendingCorrespondenceId = d.PendingCorrespondenceId
WHERE rootid = @P0 and dgr.name in('cust_pn', 'case_pn')
UNION
SELECT distinct d.documentpath as path, d.documentname as name, d.datecreated as created, pc.DateProcessed
FROM datagatheringruntime dgr 
INNER JOIN processentitymapping pem on pem.entityid = dgr.entityid 
INNER JOIN document d on d.unitofworkid = pem.processid
LEFT JOIN PendingCorrespondence pc on pc.PendingCorrespondenceId = d.PendingCorrespondenceId
WHERE rootid = @P0 and dgr.name in('cust_pn', 'case_pn')
OPTION(RECOMPILE);

The reason being that the table spool is feeding the NESTED LOOPS operator

And on this nested loops operator is the OR predicate.

Filtering out until we have 9 rows remaining.

Changing the OR to a UNION should remove the spool, you might have to look into indexing after removing the OR.

Indexes that could improve performance after rewriting with UNION

CREATE INDEX IX_EntityId
on document(EntityId)
INCLUDE(DocumentPath, DocumentName, DateCreated, PendingCorrespondenceId);

CREATE INDEX IX_UnitOfWorkId
on document(UnitOfWorkId)
INCLUDE(DocumentPath, DocumentName, DateCreated, PendingCorrespondenceId);

See here for another example on this

Related Solutions

Sql-server – Tuning a query with temp table join

Why does the inner join to a one record temp table make the query take so much longer time?

Without the join, the optimizer is smart enough to work out that it can find the minimum value by reading one row from the end of the index.

Unfortunately, it is not currently equipped to apply the same sort of logic when the query is more complicated (with a join or grouping clause, for example). To work around this limitation, you can rewrite the query to compute local minimums per row in the temporary table, then find the global minimum.

Perhaps the easiest way to express this in T-SQL is to use the APPLY operator:

SELECT
    -- Global minimum
    @tenor_from = MIN(MinMaturityPerCurveID.maturity_date)
FROM #source_price_curve_list AS SPCL
CROSS APPLY
(
    -- Minimum maturity_date per price_curve_id
    SELECT TOP (1) 
        SPC.maturity_date
    FROM  dbo.source_price_curve AS SPC
    WHERE
        SPC.source_curve_def_id = SPCL.price_curve_id
         and as_of_date >= @as_of_date_from 
    ORDER BY
        SPC.maturity_date ASC
) AS MinMaturityPerCurveID;

Good performance relies on there being many rows per price_curve_id. You may need an index of the form:

CREATE NONCLUSTERED INDEX
    [IX dbo.source_price_curve source_curve_def_id, maturity_date, as_of_date]
ON dbo.source_price_curve 
(
    source_curve_def_id,
    maturity_date,
    as_of_date
);

Sql-server – Row estimates always too low

(summarizing my comments and putting as answer)

A query rewrite will solve the issue of getting low row estimates. As Joe Chang explains in his blog post Query Optimizer Gone Wild - Full-Text

CONTAINS is "a predicte used in a WHERE clause" per Microsoft documentation, while CONTAINSTABLE acts as a table.

You get a much better plan (merge join) using CONTAINSTABLE vs the actual plan using contains uses a nested loop join with low row estimates.

You can rewrite the query as :

SELECT TOP 30 p.PersonId,
              p.PersonParentId,
              p.PersonName,
              p.PersonPostCode
FROM dbo.People p
left join containstable (ContactFullText, '"mr" AND "ch*"') cf on cf.[yourKey] = p.PersonId
WHERE p.PersonDeletionDate IS NULL
      AND p.PersonCustomerId = 24
      --AND CONTAINS(ContactFullText, '"mr" AND "ch*"')
      AND p.PersonGroupId IN(197, 206, 186, 198)
      AND [RANK] > 0
ORDER BY p.PersonParentId,
         p.PersonName;

Best Answer

Related Solutions

Sql-server – Tuning a query with temp table join

Sql-server – Row estimates always too low

Related Question