June 2021 – Sql Server

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

Sql server 2019, I have this query, and I was wondered from where is coming the estimate of 19140:

select
rezProcesat
from tblRezervari
where 1=1
and rezProcesat is null
OPTION
(
QUERYTRACEON 3604, QUERYTRACEON 2363 –New CE Stats Report
)

And the estimate for it is: 19140

the output from TF2363 :

Begin selectivity computation

Input tree:

LogOp_Select

  CStCollBaseTable(ID=1, CARD=1.84981e+07 TBL: tblRezervari)

  ScaOp_Comp x_cmpIs

      ScaOp_Identifier QCOL: [Stefan_2019].[dbo].[tblRezervari].rezProcesat

      ScaOp_Const TI(datetime,Null,ML=8) XVAR(datetime,Not Owned,Value=NULL)

Plan for computation:

CSelCalcColumnInInterval

  Column: QCOL: [Stefan_2019].[dbo].[tblRezervari].rezProcesat

Loaded histogram for column COL: KeyCo2 from stats with id 13

Selectivity: 0.00103472

Stats collection generated:

CStCollFilter(ID=2, CARD=19140.4)

  CStCollBaseTable(ID=1, CARD=1.84981e+07 TBL: tblRezervari)

End selectivity computation

So, the value 19140 is base on 0.00103472 * 1.84981e+07

But , from where did we get the **Selectivity: 0.00103472** ?
I have a filtered index on that column, with filtered clause on exact that predicate.

Now, the next value : CARD=1.84981e+07, translated to 18498100

This should be the cardinality of the table… but I don’t from where is that value.

I don’t exactly know from where the exact value 18498100 come from , but this probably would be a future post after more research

S

Month: June 2021

Puzzle CE 2

Puzzle CE