Sql-server – Auto Update Stats not updating Statistics

sql serversql-server-2012statistics

Am using SQL SERVER 2012 I have my Auto Update Stats ON in my database.

From the below link I learned that, the Auto Update stats will fire for every SQRT(1000 * Table rows) change in table rows.
https://blogs.msdn.microsoft.com/srgolla/2012/09/04/sql-server-statistics-explained/

I created a table with 1000 records 

SELECT TOP 500 Row_number()OVER (ORDER BY (SELECT NULL)) rn,
                name
INTO   stst
FROM   sys.objects

Creating statistics 

CREATE STATISTICS rn  
    ON stst (rn)  

CREATE STATISTICS name  
    ON stst (name)

Checking the created Statistics 

DBCC show_statistics('stst', rn)  -- Rows 500 

DBCC show_statistics('stst', name) -- Rows 500

As per the formula 

select SQRT(1000 * 500) -- 707.106781186548

So If I add/modify 707.106781186548 records in my table auto update statistics should fire

Add 1000 more records to my table which should be more than enough to fire auto update stats

INSERT INTO stst(rn,name)
SELECT TOP 1000 Row_number()OVER (ORDER BY (SELECT NULL)) rn,
                a.name
FROM   sys.objects a

To fire the auto update stats

Select * from stst

Checking the statistics 

DBCC show_statistics('stst', rn)  -- Rows 500 

DBCC show_statistics('stst', name) -- Rows 500

Unfortunately still the Rows is 500 only.

Even after inserting 1000 records into my table which is obviously greater than 707.106781186548 while performing SELECT why Auto Update stats didn't fire ? What am I missing here

Best Answer

The new calculation is only used if trace flag 2371 is enabled, except on SQL Server 2016 when the context database compatibility level is set to 130, where it is the default behaviour. See Microsoft KB 2754171:

Controlling Autostat (AUTO_UPDATE_STATISTICS) behavior in SQL Server

Statistics are not updated when data is modified. A statistics update is triggered when cost-based optimization finds that interesting statistics for the query are stale.

The optimizer does not enter cost-based optimization for very simple ("trivial") queries, where a single obvious plan is always optimal. No statistics updates occur in that scenario.

See the Microsoft White Paper Plan Caching and Recompilation in SQL Server 2012 by Greg Low.

Related Question