How to control position of startup predicate in the execution plan

Question

For two last statements of the code below produce actual execution plan. You can see that startup predicate on @Par1 is placed in a different position that completely changes the actual number of rows that come from test_fn1 function. I need to control this behaviour.

create or alter function dbo.test_fn1(@Par1 varchar(100), @Par2 varchar(1))
returns @t table(item varchar(100))
as
begin
  insert into @t (item) select value from STRING_SPLIT(@Par1, @Par2);
  return
end
GO
create or alter function dbo.test_fn(@Par1 varchar(100), @Par2 varchar(100))
returns table
as
return (
  select s.*
    from dbo.test_fn1(@Par2,';') x
    inner join sys.objects s on s.name = x.item
    where @Par1 = 'CASE1'
)
GO
create or alter function dbo.test_fnx(@Par1 varchar(100), @Par2 
   varchar(100))
returns table
as
return (
  select s.*
    from sys.objects s
    inner join dbo.test_fn1(@Par2,';') x ON s.name = x.item
    where @Par1 = 'CASE1'
)
GO
declare @Par1 varchar(100), @Par2 varchar(100)
select @Par1 = 'CASE2', @Par2 = 'test1;test2'

select * from dbo.test_fn(@Par1, @Par2)
select * from dbo.test_fnx(@Par1, @Par2)

Below is the proper plan where startup predicate makes the query does not work on any data.

Here is the plan that show wrong behaviour of placing startup predicate. In both cases we start from the same function and only order in T-SQL is changed.

Tested on SQL Server 2016 SP2.

Is there any whitepaper or documentation on how SQL Server is placing startup predicates?

Answer 1

Is there any whitepaper or documentation on how SQL Server is placing startup predicates?

No, not that I've ever seen. They don't get written about all that much. The one time I blogged about them, I did a lot of head scratching.

Now, I have an answer -- it's not guaranteed to always work. It's playing some tricks on the optimizer that you can learn more about by watching Query Tuning Mastery: Clash of the Row Goals, a talk by Adam Machanic.

With that out of the way, we can control to some degree where the optimizer decides to stick predicates by using TOP.

Function rewrite 1:

In this one, the WHERE clause appears inside the CROSS APPLY.

CREATE OR ALTER FUNCTION dbo.test_erik ( @Par1 VARCHAR(100), @Par2 VARCHAR(100))
RETURNS TABLE
AS
    RETURN (   SELECT s.*
               FROM   sys.objects AS s
               CROSS APPLY
                      (   SELECT TOP (2147483647) *
                          FROM   dbo.test_fn1(@Par2, ';') AS x
                          WHERE  s.name = x.item
                          AND    @Par1 = 'CASE1' ) AS ca );
GO

Function rewrite 2:

In this one, the WHERE clause appears outside the CROSS APPLY.

CREATE OR ALTER FUNCTION dbo.test_erikx ( @Par1 VARCHAR(100), @Par2 VARCHAR(100))
RETURNS TABLE
AS
    RETURN (   SELECT s.*
               FROM   sys.objects AS s
               CROSS APPLY
                      (   SELECT TOP (2147483647) *
                          FROM   dbo.test_fn1(@Par2, ';') AS x
                          WHERE  s.name = x.item ) AS ca
               WHERE  @Par1 = 'CASE1' );
GO

You can see the resulting plans here.

In plan 1, the startup expression predicate appears inside by the TOP:

In plan 2, it appears outside.

If you want the Startup Expression Predicate to act as a constant scan, try this:

CREATE OR ALTER FUNCTION dbo.test_erik_filter ( @Par1 VARCHAR(100), @Par2 VARCHAR(100))
RETURNS TABLE
AS
    RETURN (   
               SELECT ca.*
               FROM (
                     SELECT TOP (1) 1 AS n 
                     WHERE @Par1 = 'CASE1'
                    ) AS x
               CROSS APPLY 
                (
               SELECT     s.*
               FROM       sys.objects AS s
               INNER JOIN dbo.test_fn1(@Par2, ';') AS x
                   ON s.name = x.item                  
               ) AS ca

         );
GO

If we run two different test cases, the plans will be slightly different:

DECLARE @Par1 VARCHAR(100), @Par2 VARCHAR(100);
SELECT @Par1 = 'CASE2', @Par2 = 'test1;test2';

select * from dbo.test_erik_filter(@Par1, @Par2)

GO 

DECLARE @Par1 VARCHAR(100), @Par2 VARCHAR(100);
SELECT @Par1 = 'CASE1', @Par2 = 'test1;test2';

select * from dbo.test_erik_filter(@Par1, @Par2)

GO

Looking at the live query plans (here are the regular ones), there are a couple early important differences.

No rows come out of this, which means the start up expression isn't true.

Rows will flow out of this one, because it does.

Remember that data flows from right to left in a query plan. Putting the filter further towards the left side of the plan has no advantage for doing less work.

Aaron Bertrand discusses a similar issue with regular filters here:

• Should I use NOT IN, OUTER APPLY, LEFT OUTER JOIN, EXCEPT, or NOT EXISTS?

As an aside, if your real-life code doesn't just select from system tables/views, you should consider adding the SCHEMABINDING attribute to it, which could aid in filter placement.

Hope this helps!

Answer 2

A good friend of mine Grzegorz (Stasiek) Domański found the solution that makes the query check startup predicate at first. Here is the code (similar to Erik solution).

 create or alter function dbo.test_fn1(@Par1 varchar(100), @Par2 varchar(1))
 returns @t table(item varchar(100))
 as
 begin
   insert into @t (item) select value from STRING_SPLIT(@Par1, @Par2);
   return
 end
 GO
 create or alter function dbo.test_fn(@Par1 varchar(100), @Par2 varchar(100))
 returns table
 as
 return (
   select sx.*
   from (select top 1 null x) a
   cross apply(
     select s.*
       from dbo.test_fn1(@Par2,';') x
       inner join sys.objects s on s.name = x.item
   ) sx
   where @Par1 = 'CASE1'
 )
 GO
 create or alter function dbo.test_fnx(@Par1 varchar(100), @Par2 
    varchar(100))
 returns table
 as
 return (
   select sx.*
   from (select top 1 null x) a
   cross apply(
     select s.*
       from sys.objects s
       inner join dbo.test_fn1(@Par2,';') x ON s.name = x.item
   ) sx
   where @Par1 = 'CASE1'
 )
 GO
 declare @Par1 varchar(100), @Par2 varchar(100)
 select @Par1 = 'CASE2', @Par2 = 'test1;test2'

 select * from dbo.test_fn(@Par1, @Par2)
 select * from dbo.test_fnx(@Par1, @Par2)

Here are the execution plans. Marked places are the moments of startup predicate checks. And these are actual plans so no records are considered as long as startup predicate is false. And that is we expected to have.