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
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.