SQL Server Query Plans: Startup Expression Predicate

I’ve already posted about predicates in query plans, but here is one more: Startup Expression Predicate. Again, it’s better to illustrate its behavior by example. Let’s create a small table with one clustered index and put some data into it.

create table dbo.startup_expression_predicate_example (
	dt datetime not null,
	some_data char(1000) null
);

insert into dbo.startup_expression_predicate_example (dt)
select top (100000)
	dateadd(second, cast(floor(rand(checksum(newid())) * 3600 * 24 * 10) as int), '20160101')
from sys.all_columns as t1
cross join sys.all_columns as t2;

create clustered index ix_cl_seek_predicate_example on dbo.startup_expression_predicate_example (dt);

Now, look at the following query. It should calculate the amount of rows in the table, but only if @return_anything flag is on.

set statistics io on;
set statistics time on;

declare
	@from_v1 datetime = '20160101',
	@to_v3 datetime = '20160108',
	@return_anything bit = 1;

select count(*)
from [dbo].[startup_expression_predicate_example] as t
where
	@return_anything = 1
	and (
		t.[dt] >= @from_v1
		and t.[dt] < @to_v3
	);

The query is returning about 70 thousand rows and does a certain amount of logical reads.

Table ‘startup_expression_predicate_example’. Scan count 1, logical reads 10042, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 15 ms, elapsed time = 17 ms.

Let’s look at the query plan now.

We see the Filter operator with Startup Expression Predicate before Clustered Index Seek. The SQL Server fires the whole subtree to the left from the Filter operator only when its expression is true. Therefore, if we try to execute the same query with @return_anything equals to zero, it won’t even touch the table.

declare
	@from_v1 datetime = '20160101',
	@to_v3 datetime = '20160108',
	@return_anything bit = 0;

select count(*)
from [dbo].[startup_expression_predicate_example] as t
where
	@return_anything = 1
	and (
		t.[dt] >= @from_v1
		and t.[dt] < @to_v3
	);

Execution statistics is quite simple.

Table ‘Worktable’. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 0 ms.

You can encounter this query plan in different situation, don’t be afraid of it, it’s a great optimization that make execution simpler by skipping the whole parts of the query plans if they are not needed.