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);