I ran Brent Ozar's BlitzCache and it identified a query that is executing with Forced Serialization. I'm assuming it is because of the Calendar functions contained with the query. I was looking for options to remedy it and learned that the Froid feature in SQL Server 2019 is supposed to prevent Forced Serialization from occurring. I have the database set to 2019 (150) compatibility. Is there a reason this query is not benefitting from Froid?
select
DATEADD(q, DATEDIFF(q, 0, MonthOfDate), 0) as Quarter
,[PartnerCD]
,[PartnerNM]
,[PartnerGRPNM]
,[CCOMemberID]
,[MemberID]
,[CCOType]
,[RateCD]
,[Rate]
,sum([MemberMonth]) as MemberQuarter
,[CurrentAge]
,[AgeAtTime] = CASE WHEN dbo.fn_CalculateAge(BirthDT, (DATEADD(q, DATEDIFF(q, 0, MonthOfDate), 0)), 'YEAR') < 0 THEN NULL ELSE dbo.fn_CalculateAge(BirthDT, (DATEADD(q, DATEDIFF(q, 0, MonthOfDate), 0)), 'YEAR') END
,AgeCategory = dbo.fn_CalculateAgeCatYrsOrdered(BirthDT, (DATEADD(q, DATEDIFF(q, 0, MonthOfDate), 0)))
,[BirthDT]
,[Gender]
,[Language]
,[RaceEthnicity]
,[ZipCD]
FROM [BIP].[QDS].[Enrollment_Monthly]
group by DATEADD(q, DATEDIFF(q, 0, MonthOfDate), 0)
,[PartnerCD]
,[PartnerNM]
,[PartnerGRPNM]
,[CCOMemberID]
,[MemberID]
,[CCOType]
,[RateCD]
,[Rate]
,[CurrentAge]
,[BirthDT]
,[Gender]
,[Language]
,[RaceEthnicity]
,[ZipCD]
Query plan is available in case that is helpful. https://www.brentozar.com/pastetheplan/?id=S1L0EMrdo