The seven reasons why Auto Update Stats event will not trigger despite how many modifications affect any of the tables involved in a compiled plan?

A colleague from PFE recently asked the following:

******************************

Hi Folks,

I’m trying to find out when the Auto Update Stats event is generated assuming Auto Update Stats and Auto Create Stats db options are enabled.

As far as I know the stats should be invalidated when the recompilation threshold (RT) has been reached.

So for a small table e.g. containing one row the RT representing by ColModCtr is 500.

When is execute the code below  (TEST#1) I cannot see the Auto Stats event using XEvents on SQL2012 nor using Profiler on SQL2008R2.

My colleague Grzegorz tested it on SQL 2008 and it does seem to work as expected.

TEST #1

1. Create Table

Create Table dbo.Tab

(

id int,

Col varchar(10)

)

2. Create a test procedur3

create procedure dbo.Test

@id int

as

begin

select * from dbo.Tab where id=@id

end

3. Insert one row into table

Insert into dbo.Tab

Values (1, 'a')

4. Execute the procedure to trigger Auto Stats generation

exec dbo.Test 1

5. Insert 600 rows to reach the recompilation threshold

Insert into dbo.Tab

Values (1, 'a')

go 200

Insert into dbo.Tab

Values (2, 'b')

go 300

Insert into dbo.Tab

Values (3, 'c')

go 100

6. Execute the procedure again

Set statistics profile on

exec dbo.Test 1

Has anyone come across this behavior? I cannot find any info on the behavior change.

Thanks in advance.

Cheers,

Dominik

******************************

And this is the answer to Dominik’s inquiry:

It is the expected behavior.

I haven’t tested it myself in all currently supported versions but based on what I’ve seen implemented in the source code, it should behave exactly the same on SQL Server 2005, 2008, 2008 R2, and 2012. That logic hasn’t suffered modifications since Yukon.

When SQL computes the number of updates to the table which should trigger a recompile, it sets that limit to zero (meaning it will never recompile regardless of how many updates have affected that table) under any of the following seven reasons:

1. This instance tried to update the stats and failed, we should ignore the rowmodctr to prevent endless optimize-recompile loops.

2. The query is being run in autopilot mode (i.e. by one of the sessions created by Database Engine Tuning Advisor to fine tune a given query or workload).

3. This is a READONLY table/database.

4. Trivial optimization plan was found. <--This is the case in the example Dominik described above.

5. User supplied 'KEEPFIXED PLAN' hint.

6. Internal QP plans where autostats are disabled. This avoids infinite recompiles.

7. When traceflag 3608 is enabled.

Thanks,

Nacho