Converted a Scalar function to a TVF function for parallel execution-Still running in Serial mode

Forrest is mostly right, but the finer details are:

SQL Server can't parallelize modifications to table variables, which your function uses.

Prior to SQL Server 2017's Interleaved Execution, row estimates from Multi-Statement Table Valued Functions were very low.

One side effect of this is that plans were costed very poorly on the low end, and often wouldn't break cost threshold for parallelism.


SQL Server cannot parallelize multi-statement TVFs, which is what yours is. Only Inline TVFs can be parallelized.


is it possible to convert my scalar function to Inline TVF?

Yes. Something like the below would do it.

It is still pretty hefty and if run correlated would likely be quite inefficient. As Aaron points out in the comments you are calling this with constant values though so hopefully the query plan reflects this and only runs it once.

CREATE FUNCTION [dbo].[FindEventReviewDueDateInline] (@EventNumber VARCHAR(20),
                                                      @EventID     VARCHAR(25),
                                                      @EventIDDate BIT)
RETURNS TABLE
AS
    RETURN
      WITH X
           AS (SELECT cis.EventStatus AS CurrentEventStatus,
                      r.EventDateTime
               FROM   CurrentEventStatus cis
                      INNER JOIN Event1 r
                              ON cis.Event1Id = r.Id
               WHERE  r.EventNumber = @EventNumber
                      AND r.EventID = @EventID
                      AND cis.EventStatus IN ( '0', '6' )
                      AND @EventIDDate = 1)
      SELECT X.CurrentEventStatus,
             X.EventDateTime,
             CA4.ReviewDueDate
      FROM   X
             --SET @ReviewDueDate = DATEADD(DAY, 30, @EventDateTime)
             CROSS APPLY(VALUES(DATEADD(DAY, 30, X.EventDateTime))) CA1(ReviewDueDate)
             -- WHILE @ReviewDueDate < getdate() 
             --       SET @ReviewDueDate = DATEADD(DAY, 30, @ReviewDueDate)
             CROSS APPLY(VALUES( IIF(CA1.ReviewDueDate >= GETDATE(), CA1.ReviewDueDate, DATEADD(DAY, 30 * CEILING(( IIF(CAST(GETDATE() AS TIME) > CAST(CA1.ReviewDueDate AS TIME), 1, 0)
                                                                                                           + DATEDIFF(DAY, CA1.ReviewDueDate, GETDATE()) ) / 30.0), CA1.ReviewDueDate)))) CA2(ReviewDueDate)
             --SELECT @EventDateJournalDate = ....
             CROSS APPLY(SELECT TOP 1 ij.Date
                         FROM   EventPage_EventJournal ij
                                INNER JOIN EventJournalPages p
                                        ON ij.PageId = p.Id
                                INNER JOIN Journal f
                                        ON p.FormId = f.Id
                                INNER JOIN Event1 r WITH (NOLOCK)
                                        ON ( f.Event1Id = r.Id )
                         WHERE  ( r.EventNumber = @EventNumber
                                  AND r.EventID = @EventID )
                                AND ij.ReviewType = 'Supervisor Monthly Review'
                         ORDER  BY ij.Date DESC) CA3(EventDateJournalDate)
             -- IF(DATEADD(DAY, 30, @EventDateTime) < getdate()
             CROSS APPLY(VALUES ( CASE
                          WHEN ( DATEADD(DAY, 30, X.EventDateTime) < GETDATE()
                                 AND ( CA3.EventDateJournalDate IS NULL
                                        OR DATEADD(DAY, 30, CA3.EventDateJournalDate) < GETDATE() )
                                 AND DATEADD(DAY, 14, CA2.ReviewDueDate) > DATEADD(DAY, 30, GETDATE()) )
                            THEN DATEADD(DAY, -30, CA2.ReviewDueDate)
                          WHEN( ( CA3.EventDateJournalDate IS NOT NULL )
                                AND ( DATEADD(DAY, 30, CA3.EventDateJournalDate) >= CA2.ReviewDueDate ) )
                            THEN DATEADD(DAY, 30, CA2.ReviewDueDate)
                          ELSE CA2.ReviewDueDate
                        END )) CA4(ReviewDueDate);