0

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

3
  • 3
    Have a look at the scalar udf requirements, which may help you figure out why your function can't be inlined. Dec 12, 2022 at 21:26
  • Cripes - that has more restrictions than a Groupon. Since the functions are calculating current age my query gets excluded because it uses GETDATE().
    – Don
    Dec 12, 2022 at 22:23
  • @Don Scalar UDFs are still not great in 2019. I wouldn't rely on them getting inclined (meaning they'll at best still be executed RBAR), and in general still recommend them being avoided.
    – J.D.
    Dec 13, 2022 at 13:43

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Browse other questions tagged or ask your own question.