Stored Procedure started running slowly I have a stored procedure in production which started running slower all of a sudden. I did some investigation and found out that it is using a massive table which has 75 million rows and is growing. Looking at the plan it doesn't show any major missing indexes in the warning. This procedure used to take few minutes earlier however its taking 12 mins now. 

Below is the query plan:

https://www.brentozar.com/pastetheplan/?id=ryTS3fIiH

Any help is appreciated.
First issue that I have noticed in the provided plan is --> There are so many implicit conversions as listed below:

&lt;PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT_IMPLICIT(nvarchar(4),[fe].[plan_variation_subdivision_code],0)"/&gt;
              &lt;PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT_IMPLICIT(nvarchar(50),[fe].[subscriber_number],0)"/&gt;
              &lt;PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT_IMPLICIT(nvarchar(4),[fe].[plan_variation_subdivision_code],0)"/&gt;
              &lt;PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT_IMPLICIT(nvarchar(50),[fe].[subscriber_number],0)"/&gt;
              &lt;PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT_IMPLICIT(nvarchar(4),[fe].[plan_variation_subdivision_code],0)"/&gt;
              &lt;PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT_IMPLICIT(nvarchar(50),[fe].[subscriber_number],0)"/&gt;
              &lt;PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT_IMPLICIT(nvarchar(4),[fe].[plan_variation_subdivision_code],0)"/&gt;
              &lt;PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT_IMPLICIT(nvarchar(50),[fe].[subscriber_number],0)"/&gt;
              &lt;PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT_IMPLICIT(nvarchar(4),[fe].[plan_variation_subdivision_code],0)"/&gt;
              &lt;PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT_IMPLICIT(nvarchar(50),[fe].[subscriber_number],0)"/&gt;
              &lt;PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT_IMPLICIT(nvarchar(4),[fe].[plan_variation_subdivision_code],0)"/&gt;
              &lt;PlanAffectingConvert ConvertIssue="Seek Plan" Expression="CONVERT_IMPLICIT(nvarchar(50),[fe].[subscriber_number],0)=CONVERT_IMPLICIT(nchar(11),[f].[subscriber_number],0)"/&gt;
              &lt;PlanAffectingConvert ConvertIssue="Seek Plan" Expression="CONVERT_IMPLICIT(nvarchar(4),[fe].[plan_variation_subdivision_code],0)=N'0002'"/&gt;
              &lt;PlanAffectingConvert ConvertIssue="Seek Plan" Expression="CONVERT_IMPLICIT(nvarchar(50),[fe].[subscriber_number],0)=CONVERT_IMPLICIT(nchar(11),[f].[subscriber_number],0)"/&gt;
              &lt;PlanAffectingConvert ConvertIssue="Seek Plan" Expression="CONVERT_IMPLICIT(nvarchar(4),[fe].[plan_variation_subdivision_code],0)=N'0008'"/&gt;
              &lt;PlanAffectingConvert ConvertIssue="Seek Plan" Expression="CONVERT_IMPLICIT(nvarchar(50),[fe].[subscriber_number],0)=CONVERT_IMPLICIT(nchar(11),[f].[subscriber_number],0)"/&gt;
              &lt;PlanAffectingConvert ConvertIssue="Seek Plan" Expression="CONVERT_IMPLICIT(nvarchar(4),[fe].[plan_variation_subdivision_code],0)=N'0017'"/&gt;
              &lt;PlanAffectingConvert ConvertIssue="Seek Plan" Expression="CONVERT_IMPLICIT(nvarchar(50),[fe].[subscriber_number],0)=CONVERT_IMPLICIT(nchar(11),[f].[subscriber_number],0)"/&gt;
              &lt;PlanAffectingConvert ConvertIssue="Seek Plan" Expression="CONVERT_IMPLICIT(nvarchar(4),[fe].[plan_variation_subdivision_code],0)=N'0019'"/&gt;
              &lt;PlanAffectingConvert ConvertIssue="Seek Plan" Expression="CONVERT_IMPLICIT(nvarchar(50),[fe].[subscriber_number],0)=CONVERT_IMPLICIT(nchar(11),[f].[subscriber_number],0)"/&gt;
              &lt;PlanAffectingConvert ConvertIssue="Seek Plan" Expression="CONVERT_IMPLICIT(nvarchar(4),[fe].[plan_variation_subdivision_code],0)=N'0004'"/&gt;
              &lt;PlanAffectingConvert ConvertIssue="Seek Plan" Expression="CONVERT_IMPLICIT(nvarchar(50),[fe].[subscriber_number],0)=CONVERT_IMPLICIT(nchar(11),[f].[subscriber_number],0)"/&gt;
              &lt;PlanAffectingConvert ConvertIssue="Seek Plan" Expression="CONVERT_IMPLICIT(nvarchar(4),[fe].[plan_variation_subdivision_code],0)=N'0020'"/&gt;


Due to this issue you have issue in cardinality estimate and also in Seek Plan.

Please read more on this from below links:

https://www.sqlskills.com/blogs/jonathan/implicit-conversions-that-cause-index-scans/

https://hackernoon.com/are-implicit-conversions-killing-your-sql-query-performance-70961e547f11

https://www.sqlservercentral.com/forums/topic/type-conversion-may-affect-cardinalityestimate

https://www.sqlshack.com/implicit-conversion-in-sql-server/

Whenever you see yellow mark on the Select box in the query plan, please hover that and see what is the warning. Most of the time, it would give you very meaningful feedback.

Another issue I see is with the table CDB_MEMBER, its going for clustered index scan, meaning full table scan and if you would have noticed there is minor difference in estimates and actuals suggesting stats are a bit out of sync. Important aspect here is missing NC index which should have columns CDB_MBR_COV_REC_ID and SBSCR_ID

I think above two should help you a bit in fine-tuning the execution and reduce duration.

Please let us know if this helped.