SQL Server using index scan instead of seek, all of a sudden

Question

We have a stored procedure that runs daily. In one of the sub queries (query 20, in the execution plan), SQL Server was using an index seek till 2021/09/24. But all of a sudden when the query ran on 2021/09/25, SQL Server started using an index scan at the same step.

Below is the plan for 2021/09/24 where it is performing a seek

Below is the plan for 2021/09/25 where SQL Server is performing an index scan

The EMAIL_SENDS_CCMP_LTD table is a pretty large table, and below is the data for space usage of the table

Below is the query in the stored procedure which is highlighted above -

insert into  TALBOTS_BASE.dbo.EMAIL_ACTIVITIES
(EMAILTYPE,INTSOURCE,EMAIL,ACCTNO,FNAME,LNAME,EMAILDATE,DNEFLAG,SOURCE_CD,CREATE_ID,EMAILPREF,HASH,FILENAME,SEQ,FILEDATE,MODDATE,IPADDRESS,HAV_EMAILDATE)
SELECT 
      'OPEN' AS EMAILTYPE
      ,'CCMP' AS INTSOURCE 
      , a.[P_email] AS EMAIL
      ,'' AS ACCTNO
      ,'' AS [FNAME]
      ,'' AS [LNAME]
      ,CASE WHEN ISNULL(c.Subchannel,'')<>'HWW' 
       THEN
            CASE WHEN ISDATE([click_time])=1 THEN CAST([click_time] AS DATE) ELSE NULL END
      ELSE NULL
      END AS EMAILDATE
      ,'' as DNEFLAG
      ,'' AS SOURCE_CD
      ,'' AS CREATE_ID
      ,null as EMAIL_CONTACT_PREFERENCE
      ,a.HASH
      ,a.FILENAME
      ,a.SEQ
      ,a.FILEDATE
      ,a.MODDATE
      ,a.ip_address
      ,CASE WHEN c.Subchannel='HWW' 
       THEN
            CASE WHEN ISDATE([click_time])=1 THEN CAST([click_time] AS DATE) ELSE NULL END--HAVEN
      ELSE NULL
      END AS HAV_EMAILDATE
FROM [TALBOTS_RAW].[dbo].[EMAIL_OPENS_CCMP_LTD] a
left join #activitiesfiles b 
on a.FILENAME = b.FILENAME 
LEFT JOIN [TALBOTS_RAW].[dbo].[EMAIL_SENDS_CCMP_LTD] c 
ON a.p_email = c.p_email
AND a.msg_id = c.msg_id  
WHERE b.FILENAME is null

There is a non-clustered index on EMAIL_SENDS_CCMP_LTD table which on p_email(VARCHAR) and msg_id(VARCHAR) columns with included column as Subchannel(VARCHAR).

My question is on what parameters does SQL Server decide to change the query plan and is there a way to allow SQL Server to use the seek instead of a scan.

Pasting the actual plan

Query plan for 2021/09/24

Query plan for 2021/09/25

Answer 1

El Filter

The reason your plan changed is because the cardinality estimate for how many rows will pass through the filter operator changed pretty drastically.

This is sort of a tough thing to estimate generally -- you're using a left join to find rows that don't exist in #activitiesfiles -- which is often better expressed using NOT EXISTS.

FROM [TALBOTS_RAW].[dbo].[EMAIL_OPENS_CCMP_LTD] a
LEFT JOIN [TALBOTS_RAW].[dbo].[EMAIL_SENDS_CCMP_LTD] c 
    ON  a.p_email = c.p_email
    AND a.msg_id = c.msg_id  
WHERE NOT EXISTS
      (
          SELECT
              1/0
          FROM #activitiesfiles b 
          WHERE a.FILENAME = b.FILENAME 
      );

It also looks like you're not making things easier on yourself here, since your email opens table doesn't appear to have a useful index on it.

I realize that's not the part of the plan you have a problem with, but it's worth noting since the concern is with the join algorithm chosen.

You may get a better overall plan with an index on filename, msg_id, p_email that includes the columns you're selecting from EMAIL_OPENS_CCMP_LTD.

It's a little tough to work that out since your columns aren't all prefixed with the aliases you've given your tables, so I'll leave that up to you.

Last, you should at least be aware of how SQL Server caches temporary objects and statistics in query plans, which Paul White explains in these three posts:

• SQL Server Temporary Object Caching
• Temporary Table Caching in Stored Procedures
• Temporary Table Caching Explained