How to help SQL Server prepare a better plan?

Question

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:

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

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

Posting your query, and the actual execution plan ([Paste the Plan](https://www.brentozar.com/pastetheplan/)) will help here; without it we can all but guess. My stab in the dark though: you aren't using a Table Variable are you? — Thom A, May 17 '19 at 16:07
Spot on! A table var looked a better option than using a temp table — ulu, May 17 '19 at 16:41
There's your problem; SQL Server (apart from 2017) assumes a table variable contains 1 row. — Thom A, May 17 '19 at 16:53
That `IN` on the Paste the Plan version is huge. You would likely be better off using a lookup table there. — Thom A, May 17 '19 at 16:59
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. — MJH, May 17 '19 at 17:50
@Larnu Would it be better to use a temp table? Or just rewrite the join in a clever way? I need to filter T_SESSION by the PATIENT_N value before joining with the other tables because it leaves just about 100 records instead of millions to join with. — ulu, May 19 '19 at 11:59
It's difficult to tell without the full query, the one in your question is not representative of your real query here. Where are all those values even coming from? — Thom A, May 19 '19 at 12:14
@Larnu I think we're mixing 2 issues: one is a temp table and the other is a huge list of IDs. The list comes from a dynamic query, it is different every time. But I _suspect_ that my problem is with the incorrect JOIN, which happens before filtering. What I'm trying to do is force the server first filter by PATIENT_N, then do the rest. If a table variable leads to a bad plan, what are my options? — ulu, May 19 '19 at 17:07
@Larnu BTW I keep saying "table variable" while I'm actually meaning a common table expression (CTE) — ulu, May 19 '19 at 17:30
A CTE isn't a table variable. A CTE, as the name suggests, is an expression. It's not an object, variable, or anything else like that. It's more similar to a `CASE` than a table variable, as both a CTE and a `CASE` are expressions. — Thom A, May 19 '19 at 17:34
@MJH can you add your comment as an answer so that I could mark it as correct? And a few words about how you spotted it, please. — ulu, Jul 14 '19 at 13:00

score 0 · Accepted Answer · answered Jul 14 '19 at 14:56

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:

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.

How to help SQL Server prepare a better plan?

1 Answers1