Puzzle CE 2

There is this query in sql server 2019:

select

rezProcesat

from dbo.tblRezervari

where 1=1

and rezProcesat is null

and rezUserName= CAST(SUSER_SNAME() as varchar(256))

What will be the estimate for it?

Let’s take each predicate and find it estimate.

For rezProcesat is null, the estimate is 19140. For this, the is a filtered index on this predicate that have rows matching predicate=18204,unfiltered_rows=17593170

select rows,unfiltered_rows,rows*1.0/unfiltered_rows as Selectivity1 ,(rows*1.0/unfiltered_rows)*18498100 as EstimatePredicate1 from sys.dm_db_stats_properties_internal(object_id(‘dbo.tblFluxRezervari’),13);

rows	unfiltered_rows	Selectivity1	EstimatePredicate1
18204	17593170	0.00103471972362001	19140.34891950

For the second predicate : rezUserName= CAST(SUSER_SNAME() as varchar(256)), we have to look at the statistics information.

DBCC SHOW_STATISTICS (‘dbo.tblRezervari’,rezUserName) wITH DENSITY_VECTOR;

All density	Average Length	Columns
0.009708738	9.014213	rezUserName

select 18498100 * 0.009708738 as EstimatePredicate2

EstimatePredicate2 : 179593.206397800

And putting all together, we get:

EstimatePredicate1 = 19140; SelectivityPredicate1 = 0.00103472

EstimatePredicate2 = 179594; SelectivityPredicate2 = 0.009708738

select 18498100 * (0.00103472*0.009708738)

185.82868252393161600

S