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