A colleague from PFE recently asked the following:
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.
1. Create Table
Create Table dbo.Tab
2. Create a test procedur3
create procedure dbo.Test
select * from dbo.Tab where id=@id
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
Values (2, 'b')
Values (3, 'c')
6. Execute the procedure again
Set statistics profile on
Has anyone come across this behavior? I cannot find any info on the behavior change.
Thanks in advance.
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.