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