• Library
  • Sample DBs
  • RU
  • About
Home » cardinality estimation » MTVF and CE Model Variation

MTVF and CE Model Variation

Posted on May 7, 2014 by Dmitry Pilugin Posted in cardinality estimation, Uncategorized
download as a pdf file PDF

This is a short quick note about multi-statement table valued functions (MTVF) and how their cardinality is estimated in the new CE framework.

In the old CE framework the MTVF had fixed estimate of one row, in the new one the estimate is still fixed, however now it is 100 rows. That’s the whole story. =)

For the optimizer, the MTVF (as long as the scalar UDF) is a black box, from the estimation prospective. It’s often considered that inline-TVF is better, because its text is embedded into the query and optimized as whole. However, I saw the examples where MTVF performed better than inline – it depends, as it used to say. In general and most of the cases inline functions are really a better choice.

If you turn on the diagnostic output TF 2363 you will see that the optimizer uses the term “black-box” literally. You may also notice, that a fixed join calculator is used to estimate the join selectivity with the fixed 100 row estimate.

56

Now let’s move to the short example, this is an artificial example, just to demonstrate the possible positive effect of estimating more than 1 row.

At first, let’s create a Numbers table and a simple MTVF.

use AdventureWorks2012;
go

------------------------------------------------
if object_id ('dbo.mtvfGetNums') is not null drop function dbo.mtvfGetNums;
if object_id('dbo.Numbers') is not null drop table dbo.Numbers;
create table dbo.Numbers(n int primary key)
insert dbo.Numbers(n) select top(1000000) rn = row_number() over(order by (select null)) from sys.columns c1,sys.columns c2,sys.columns c3;
go

------------------------------------------------
create function dbo.mtvfGetNums(@max int)
returns @res table(id int identity primary key, n int)
with schemabinding
as
begin
	insert @res(n) select n from dbo.Numbers where n <= @max;
	return;
end;
go

Now, let’s run two identical synthetic queries, to demonstrate the difference (don’t look for the sense in them), the first one uses the old CE, the second one the new CE:

-- Old
set statistics time, xml on
declare @a int, @b int, @c int;
select 
	@a = p.BusinessEntityID,
	@b = be.BusinessEntityID,
	@c = f.n
from
	Person.Person p
	cross apply dbo.mtvfGetNums((BusinessEntityID+1)%1000) f
	left join Person.BusinessEntity be on f.id = be.BusinessEntityID
where
	p.BusinessEntityID <= 1000
option(querytraceon 9481)
set statistics time, xml off
go
--New:
set statistics time, xml on
declare @a int, @b int, @c int;
select 
	@a = p.BusinessEntityID,
	@b = be.BusinessEntityID,
	@c = f.n
from
	Person.Person p
	cross apply dbo.mtvfGetNums((BusinessEntityID+1)%1000) f
	left join Person.BusinessEntity be on f.id = be.BusinessEntityID
where
	p.BusinessEntityID <= 1000
set statistics time, xml off
go

On average the first query runs 50% slower (1950 ms old vs. 1250 ms new). Let’s look at the plans.

57

In the first case, the MTVF was estimated as one row, multiplied by the number of executions 646. The actual number of rows is much higher, about 270 000. That lower estimate, lead to selecting a merge join that demands sorted inputs, and so the sort is present. The Sort demands some memory amount, this amount is based on the cardinality also, that was underestimated and so the spill at the level two occurred.

The new CE estimated in a more correct way and is closer to reality of 270 000 rows, however, not very close, but this was enough, to choose another join type and avoid sorting and spilling.

You may invent the opposite situation, when 1 row estimate wins, or you may invent the example where there is no difference. That is possible, because 100 rows estimate is still a guess and you may vary the data to make the guess closer or farther to the reality.

The Model Variation

You may use the new CE, but turn off this particular estimate of 100 rows for MTVF using the model variation, that can be enabled by TF 9488 (it is checked in the function CCardFrameworkSQL12::CardEstimateTVF internally).

If you run the query:

set statistics time, xml on
declare @a int, @b int, @c int;
select 
	@a = p.BusinessEntityID,
	@b = be.BusinessEntityID,
	@c = f.n
from
	Person.Person p
	cross apply dbo.mtvfGetNums((BusinessEntityID+1)%1000) f
	left join Person.BusinessEntity be on f.id = be.BusinessEntityID
where
	p.BusinessEntityID <= 1000
option(querytraceon 9488)
set statistics time, xml off
go

You will see that the estimate now is 1 row per execution (i.e. 647 rows):
58

 

Interesting, that even the MTVF is estimated as in the old CE framework, we have a different plan. That is because the Joins are present in our queries, and the Join estimation was also changed in many ways. However, that is a topic for another blog post.

That’s all for that post, happy estimations! =)

download as a pdf file PDF
mtvf TF 9488
« Ascending Key and CE Model Variation
NOLOCK and Top Optimization »

Recent Posts

  • SQL Server 2017: Adaptive Join Internals
  • SQL Server 2017: How to Get a Parallel Plan
  • SQL Server 2017: Statistics to Compile a Query Plan
  • SQL Server 2017: Sort, Spill, Memory and Adaptive Memory Grant Feedback
  • SQL Server vNext: Interleaved Execution for mTVF

Archives

  • May 2017
  • April 2017
  • March 2017
  • February 2017
  • January 2017
  • July 2016
  • February 2016
  • December 2015
  • April 2015
  • March 2015
  • February 2015
  • January 2015
  • May 2014

Tags

adaptive query processing allocation order scan Ascending Key bailout bitmap bug cardinality columnstore estimation process hash join model mtvf nolock partitioning plan construction Post Optimization Rewrite PQTEConvert query optimizer query_trace_column_values rules sniffing spill sql server 2017 statistics TF 2312 TF 2329 TF 2340 TF 2363 TF 2389 TF 2390 TF 2486 TF 7357 TF 7359 TF 8628 TF 8744 TF 9115 TF 9481 TF 9482 TF 9483 TF 9488 TF 9489 top uncommited version vnext
© Dmitry Pilugin (SomewhereSomehow), 2014 - 2017