Mryntu

One of my query in was running in serial execution mode after a release and i noticed that two new functions were used in a view which is referenced in the LINQ to SQL Query generated from the application.
So i converted those SCALAR functions to TVF functions,but still the query is running in serial mode.

Earlier i have did Scalar to TVF conversion in some other queries and it solved the problem of forced serial execution.

Here is the scalar function:

CREATE FUNCTION [dbo].[FindEventReviewDueDate]
(
 @EventNumber VARCHAR(20),
 @EventID VARCHAR(25),
 @EventIDDate BIT
)

RETURNS DateTime
AS
BEGIN

DECLARE @CurrentEventStatus VARCHAR(20)
DECLARE @EventDateTime DateTime
DECLARE @ReviewDueDate DateTime


SELECT @CurrentEventStatus = (SELECT cis.EventStatus
 FROM CurrentEventStatus cis 
 INNER JOIN Event1 r WITH (NOLOCK) ON (cis.Event1Id = r.Id)
 WHERE (r.EventNumber = @EventNumber) AND r.EventID = @EventID)

SELECT @EventDateTime = (SELECT EventDateTime FROM Event1 r 
 WHERE (r.EventNumber = @EventNumber) AND r.EventID = @EventID)

IF @CurrentEventStatus IN ('0','6') AND EventIDDate = 1
BEGIN

 SET @ReviewDueDate = DATEADD(DAY, 30, @EventDateTime)

 WHILE @ReviewDueDate < getdate() 
 SET @ReviewDueDate = DATEADD(DAY, 30, @ReviewDueDate)

 DECLARE @EventDateJournalDate DateTime

 SELECT @EventDateJournalDate = (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)

 IF(DATEADD(DAY, 30, @EventDateTime) < getdate() AND
 (@EventDateJournalDate is null OR DATEADD(DAY, 30, @EventDateJournalDate) < getdate()) AND
 DATEADD(DAY, 14, @ReviewDueDate) > DATEADD(DAY, 30, getdate()))
 SET @ReviewDueDate = DATEADD(DAY, -30, @ReviewDueDate)
 ELSE IF((@EventDateJournalDate is not null ) AND (DATEADD(DAY, 30, @EventDateJournalDate) >= @ReviewDueDate))
 SET @ReviewDueDate = DATEADD(DAY, 30, @ReviewDueDate)

END
RETURN @ReviewDueDate

END

Here is the converted TVF function.

CREATE FUNCTION [dbo].[FindEventReviewDueDate_test]
(
 @EventNumber VARCHAR(20),
 @EventID VARCHAR(25),
 @EventIDDate BIT
)

RETURNS @FunctionResultTableVairable TABLE (
 CurrentEventStatus varchar(20),
 Event1DateTime DateTime,
 ReviewDueDate DateTime
 )
AS 
BEGIN

DECLARE @CurrentEventStatus VARCHAR(20)
DECLARE @EventDateTime DateTime
DECLARE @ReviewDueDate DateTime


SELECT @CurrentEventStatus = (SELECT cis.EventStatus
 FROM CurrentEventStatus cis 
 INNER JOIN Event1 r WITH (NOLOCK) ON (cis.Event1Id = r.Id)
 WHERE (r.EventNumber = @EventNumber) AND r.EventID = @EventID)

SELECT @EventDateTime = (SELECT EventDateTime FROM Event1 r 
 WHERE (r.EventNumber = @EventNumber) AND r.EventID = @EventID)

IF @CurrentEventStatus IN ('0','6') AND EventIDDate = 1
BEGIN

 SET @ReviewDueDate = DATEADD(DAY, 30, @EventDateTime)

 WHILE @ReviewDueDate < getdate() 
 SET @ReviewDueDate = DATEADD(DAY, 30, @ReviewDueDate)

 DECLARE @EventDateJournalDate DateTime

 SELECT @EventDateJournalDate = (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)

 IF(DATEADD(DAY, 30, @EventDateTime) < getdate() AND
 (@EventDateJournalDate is null OR DATEADD(DAY, 30, @EventDateJournalDate) < getdate()) AND
 DATEADD(DAY, 14, @ReviewDueDate) > DATEADD(DAY, 30, getdate()))
 SET @ReviewDueDate = DATEADD(DAY, -30, @ReviewDueDate)
 ELSE IF((@EventDateJournalDate is not null ) AND (DATEADD(DAY, 30, @EventDateJournalDate) >= @ReviewDueDate))
 SET @ReviewDueDate = DATEADD(DAY, 30, @ReviewDueDate)
 insert into @FunctionResultTableVairable
 select @CurrentEventStatus,@EventDateTime,@ReviewDueDate 

END
return;
END

GO

Is there anything wrong with my implementation of TVF function which is preventing the query to run in parallel mode.

I use the TVF function in the query as below;

select ReviewDueDate from dbo.FunctionResultTableVairable('a','b','c')

My actual query which uses the view is quite complex and if i comment out the function part in the view and on executing , the query runs in parallel.So it is function which is forcing the query to run in parallel.

My actual query is in the below format.

select 
dv.column1,
dv.column2,
---------
---------
--------
(select ReviewDueDate from dbo.FunctionResultTableVairable('a','b','c')) AS 'Columnx'
from
DemoView dv
Where 
condition1
conditon 2

Any help is appreciated.

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

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.