Inconsistent execution times for stored procedure

I have a stored procedure that runs daily. Most days it completes in about 20 minutes, some days it takes an hour. I'm trying to find the cause so I can get it to be more consistent. I looked for other jobs/processes that could be running at the same time and competing for resources, but did not see anything promising. Ran BlitzCache. First result is Plan Cache Instability with 100% plans created in the past 24 hours. But the server was just patched and rebooted last night so I think that is to be expected. The second result is Plan Warnings (warning detected in execution plan). Checked the Execution Plan and saw a recommendation for a missing index. It wants an index on PaidDate and then Include all the other columns in the table. I find the sql recommendations for indexes to be hit/miss, plus this database is a backup from a vendor and I do not have direct control to add indexes at will. Also, as long as I'm not clearing the plan cache I'm doubtful that a missing index would cause this type of inconsistency. Third is Non-SARGable queries. I've included what I believe is the most resource intensive part of the query. The final step is to INSERT into a table from the temp table. There is a CASE statement, but we use CASE statements a lot and do not have this kind of variability in other stored procedures. DROP TABLE IF EXISTS #PPNUnpivotCCO; SELECT CCOMemberID = xw.CCOMemberID, MemberID = e.PrimeID, RateCD = e.RateCode, ContractServiceAreaCD = e.ContractServiceAreaCode, EligCat = e.EligCat, ContractRateAMT = Convert(decimal(15,2),e.ContractRate), Method = e.Method, CCOPremiumAMT = Convert(decimal(15,2),e.PremiumAmount), ---Medical Medical_PartnerCD = med.PartnerCD, Medical_PartnerNM = med.PartnerNM, MedBaseAmt = Convert(decimal(15,2),e.MedBaseAmt), MedRiskRateAmt = Convert(decimal(15,6),e.MedRiskRate), --Numeric length extended to 6 past the decimal C.S. MedAdjAmt = Convert(decimal(15,2),e.MedAdjAmt), AdminWHMedAmt = Convert(decimal(15,2),e.AdminWHMed), StrategicWHMedAmt = Convert(decimal(15,2),e.StrategicWHMed), CommunityWHMedAmt = Convert(decimal(15,2),e.CommunityWHMed), ReserveWHMedAmt = Convert(decimal(15,2),e.ReserveWHMed), MedFinalAMT= Convert(decimal(15,2),e.MedFinal), ---Mental Health Behavioral_PartnerCD = beh.PartnerCD, Behavioral_PartnerNM = beh.PartnerNM, MHBaseAmt = Convert(decimal(15,2),e.MHBaseAmt), MHRiskRateAmt = Convert(decimal(15,6),e.MHRiskRate), --Numeric length extended to 6 past the decimal C.S. MHAdjAmt = Convert(decimal(15,2),e.MHAdjAmt), AdminWHMHAmt = Convert(decimal(15,2),e.AdminWHMH), StrategicWHMHAmt = Convert(decimal(15,2),e.StrategicWHMH), CommunityWHMHAmt = Convert(decimal(15,2),e.CommunityWHMH), ReserveWHMHAmt = Convert(decimal(15,2),e.ReserveWHMH), MHFinalAmt = Convert(decimal(15,2),e.MHFinal), ---NEMT NEMTBaseAmt = Convert(decimal(15,2),e.NEMTBaseAmt), NEMTWithholdAmt = Convert(decimal(15,2),e.NEMTWithhold), StrategicWHNEMTAmt = Convert(decimal(15,2),e.StrategicWHNEMT), CommunityWHNEMTAmt = Convert(decimal(15,2),e.CommunityWHNEMT), NEMTFinalAmt = Convert(decimal(15,2),e.NEMTFinal), ---Dental Dental_PartnerCD = den.PartnerCD, Dental_PartnerNM = den.PartnerNM, DenBaseAmt = Convert(decimal(15,2),e.DenBaseAmt), DenRiskRateAmt = Convert(decimal(15,2),e.DenRiskRate), DenAdjAmt = Convert(decimal(15,2),e.DenAdjAmt), AdminWHDenAmt = Convert(decimal(15,2),e.AdminWHDen), StrategicWHDenAmt = Convert(decimal(15,2),e.StrategicWHDen), CommunityWHDenAmt = Convert(decimal(15,2),e.CommunityWHDen), ReserveWHDenAmt = Convert(decimal(15,2),e.ReserveWHDen), performanceWHDenAmt = Convert(decimal(15,2),e.performanceWHDen), DenFinalAmt = Convert(decimal(15,2),e.DenFinal), MCOTaxAmt = Convert(decimal(15,2),e.MCOTax), HRAAmt = Convert(decimal(15,2),e.HRA), RevenueRAEFinalAMT = COALESCE(e.MedFinal, 0) + COALESCE(e.DenFinal, 0) + COALESCE(e.MHFinal, 0), MemberMonth= CASE WHEN e.RateCode COLLATE database_default IS NULL THEN 0 WHEN e.RateCode COLLATE database_default IN ( SELECT RateCD FROM Reference.Reference.Rate r WHERE r.ExcludeFromMM = 'Y') THEN 0 WHEN e.Prorate > 1 THEN 1 ELSE ROUND(e.Prorate, 5) END , BenefitMonth = DATEFROMPARTS(YEAR(e.BenefitBegin), MONTH(e.BenefitBegin), 1) , StartDT = Convert(date,e.BenefitBegin) , EndDT = Convert(date,e.BenefitEnd), PaidDT = Convert(date,e.PaidDate), PremiumPaymentNotificationID INTO #PPNUnpivotCCO --select e.* FROM VendorDB.dbo.Allocation e INNER JOIN Reference.Reference.CCOIdToMemberID xw -- only active members (solves the issue of multiple MemberId for a single COMemberid ON xw.CCOMemberID = e.ccoMemberID AND xw.MemberID COLLATE database_default = e.PrimeID AND xw.ActiveFLG = 1 LEFT JOIN Reference.Reference.Rate r---updated to left join, for some of the kickers, we do not have the rate codes set up in reference. ON e.RateCode COLLATE database_default = r.RateCD LEFT JOIN Reference.Reference.Partners Med ON ( e.MedPlan COLLATE database_default = Med.PartnerNM OR e.MedPlan COLLATE database_default = Med.PartnerAlt1NM OR e.MedPlan COLLATE database_default = Med.PartnerAlt2NM ) AND Med.ActiveFLG = 1 LEFT JOIN Reference.Reference.Partners beh ON ( e.MHPlan COLLATE database_default = beh.PartnerNM OR e.MHPlan COLLATE database_default = beh.PartnerAlt1NM OR e.MHPlan COLLATE database_default = beh.PartnerAlt2NM ) AND beh.ActiveFLG = 1 LEFT JOIN Reference.Reference.Partners den ON ( e.DenPlan COLLATE database_default = den.PartnerNM OR e.DenPlan COLLATE database_default = den.PartnerAlt1NM OR e.DenPlan COLLATE database_default = den.PartnerAlt2NM ) AND den.ActiveFLG = 1 WHERE 1=1 and Convert(date,e.PaidDate) >= Convert(date,DateAdd(Year,-2,GETDATE())) ---Only pulling paid the last 2 years ; DELETE Enrollment.PremiumCCOMemberMonth where PaidDT >= Convert(date,DateAdd(Year,-2,GETDATE())); INSERT INTO Enrollment.PremiumCCOMemberMonth (CCOMemberID, MemberID, RateCD, ContractServiceAreaCD, EligCat, ContractRateAMT, Method, CCOPremiumAMT, Medical_PartnerCD, Medical_PartnerNM, MedBaseAmt, MedRiskRateAmt, MedAdjAmt, AdminWHMedAmt, StrategicWHMedAmt, CommunityWHMedAmt, ReserveWHMedAmt, MedFinalAmt, Behavioral_PartnerCD, Behavioral_PartnerNM, MHBaseAmt, MHRiskRateAmt, MHAdjAmt, AdminWHMHAmt, StrategicWHMHAmt, CommunityWHMHAmt, ReserveWHMHAmt, MHFinalAmt, NEMTBaseAmt, NEMTWithholdAmt, StrategicWHNEMTAmt, CommunityWHNEMTAmt, NEMTFinalAmt, Dental_PartnerCD, Dental_PartnerNM, DenBaseAmt, DenRiskRateAmt, DenAdjAmt, AdminWHDenAmt, StrategicWHDenAmt, CommunityWHDenAmt, ReserveWHDenAmt, performanceWHDenAmt, DenFinalAmt, MCOTaxAmt, HRAAmt, RevenueRAEFinalAMT, MemberMonth, BenefitMonth, StartDT, EndDT, PaidDT, PremiumPaymentNotificationID ) SELECT CCOMemberID, MemberID, RateCD, ContractServiceAreaCD, EligCat, ContractRateAMT, Method, CCOPremiumAMT, Medical_PartnerCD, Medical_PartnerNM, MedBaseAmt, MedRiskRateAmt, MedAdjAmt, AdminWHMedAmt, StrategicWHMedAmt, CommunityWHMedAmt, ReserveWHMedAmt, MedFinalAmt, Behavioral_PartnerCD, Behavioral_PartnerNM, MHBaseAmt, MHRiskRateAmt, MHAdjAmt, AdminWHMHAmt, StrategicWHMHAmt, CommunityWHMHAmt, ReserveWHMHAmt, MHFinalAmt, NEMTBaseAmt, NEMTWithholdAmt, StrategicWHNEMTAmt, CommunityWHNEMTAmt, NEMTFinalAmt, Dental_PartnerCD, Dental_PartnerNM, DenBaseAmt, DenRiskRateAmt, DenAdjAmt, AdminWHDenAmt, StrategicWHDenAmt, CommunityWHDenAmt, ReserveWHDenAmt, performanceWHDenAmt, DenFinalAmt, MCOTaxAmt, HRAAmt, RevenueRAEFinalAMT, MemberMonth, BenefitMonth, StartDT, EndDT, PaidDT, PremiumPaymentNotificationID FROM #PPNUnpivotCCO Fourth is regarding implicit conversions. > The column [xw].[MemberID] has a data type of varchar which caused > implicit conversion on the column [e].[PrimeID]. They are both varchar, so I'm guessing the implicit conversion is a result of the COLLATE needed because this database has a different collation than the other databases on the server. Does any of this look like the probable cause or do I need to dig in other places?