0

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

2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.