sql-serverquery-optimizationsql-execution-plansql-optimization

How to help SQL Server prepare a better plan?


Sometimes my query in SQL Server takes a few minutes, and running it again takes only a fraction of a second. I assume the query optimizer made a wrong decision the first time, but collected data allowed it to do better the next time. Looking at the query plan, I see something like this sometimes:

estimated vs actual number of rows

I believe it means that the optimizer miscalculated the number of rows and made a wrong decision on e.g. how to make a join. How do I "help" it? Provide a hint? Update statistics? Add an index? Rewrite the query?

Here's more info on that part of the query (as you see, there's a clustered index already):

Key Lookup node

UPDATE: here's the query (truncated for brevity):

WITH session_temp AS 
(
    SELECT 
        SESSION_N, SESSION_DATE, SESSION_CREATE_DATE, MASTER_SESSION_N, ROOT_SESSION_N  
    FROM 
        T_SESSION 
    WHERE 
        PATIENT_N = 140945 AND SESSION_ACTIVE_FLAG = 1
)
SELECT  
    S.SESSION_DATE, S.SESSION_CREATE_DATE, 
    SUB_ST.SESSION_N, SUB_ST.XML_VALUE, 'subtemplate' AS SOURCE_TYPE 
FROM 
    session_temp SUB_S 
INNER LOOP JOIN 
    T_SESSION_TEMPLATE SUB_ST ON (SUB_S.SESSION_N = SUB_ST.SESSION_N AND SUB_S.MASTER_SESSION_N IS NOT NULL) 
JOIN
    T_SESSION_TEMPLATE ST ON (SUB_S.ROOT_SESSION_N = ST.SESSION_N) 
JOIN
    session_temp S ON ST.SESSION_N = S.SESSION_N 
JOIN
    T_SESSION_CATEGORY SC ON S.SESSION_N = SC.SESSION_N
WHERE 
    ST.TEMPLATE_N IN (1709, 1686, 1660, 1526, 1474, 1456, 1301, 1258) 
    AND SUB_ST.TEMPLATE_N IN (617) 
    AND SUB_S.MASTER_SESSION_N IS NOT NULL 
    AND S.MASTER_SESSION_N IS NULL 
    AND SC.category_n IN (241, 119, 181, 183, 110)
                        
UNION ALL

SELECT  
    SESSION_DATE, SESSION_CREATE_DATE, ST.SESSION_N, XML_VALUE, 
    'include' AS SOURCE_TYPE
FROM 
    T_SESSION_TEMPLATE ST 
JOIN  
    session_temp S ON ST.SESSION_N = S.SESSION_N
WHERE 
    ST.TEMPLATE_N IN (1709, 1686, 1660, 1526, 1474, 1456, 1301, 1258) 
    AND S.MASTER_SESSION_N IS NULL
ORDER BY 
    SESSION_DATE DESC, SESSION_N DESC 

UPDATE 2: here's the execution plan: https://www.brentozar.com/pastetheplan/?id=BJL7Bwh3V


Solution

  • Try adding XML_VALUE to the index: IX_T_SESSION_TEMPLATE_SESSION_N. This should prevent the key lookup that is causing most of your IO.

    In your execution plan, your are looking at this section:

    Partial execution plan

    If you mouse-over the Key Lookup operator (estimate of 97%), you can see it is having to do a lookup on XML_VALUE (over a million times), which can be added to the index being scanned.