Sql-server – can’t find column stats for newly created table

execution-planrecompilesql serversql server 2014statistics

I am trying to simulate a plan recompilation due to auto update stats which is triggered when threshold(500 + 20 percent modifications) reached.

database current settings are:

enter image description here

I started with creating a simple table:

create table recomp(num int)
go

Then I insert some rows:

insert into recomp
values (5)
go 100

and run my query below 5 times (I used where 1 = (select 1)) for full optimization:

select * from recomp
where 1 = (select 1)

in my plan cache I have the following:

enter image description here

Now the interesting part, I again insert new rows in order to trigger auto update stats for num column of the recomp table:

insert into recomp
values (8)
go 500

Here stats for num column has updated. The next what I am going to do is run my select query:

select * from recomp
where 1 = (select 1)

When I check my plan cache:

enter image description here

and an event which captures recompiles:

enter image description here

my question is: where can I find stats for num column?

enter image description here

Best Answer

The optimizer doen't need any stats to optimize those queries, so none are created.

When the automatic create statistics option, AUTO_CREATE_STATISTICS is ON, the Query Optimizer creates statistics on individual columns in the query predicate, as necessary, to improve cardinality estimates for the query plan.

AUTO_CREATE_STATISTICS Option

Try something like

select *
from recomp
where num = 4

And you should see a statistic created.

Related Question