Gsyfuy

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.