8

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

  • 7
    You're missing a couple things. The improved calculation is only used in versions prior to 2016 if you have Trace Flag 2371 on. Stats don't update when data is modified, they update after data is modified and a query needs to use them (assuming they've met the modification threshold). – Erik Darling Dec 21 '16 at 13:01
  • 7
    Also a trivial query (e.g. SELECT * without a WHERE clause) won't trigger a stats update. Try running a query that actually makes the engine do something where the stats might be useful (e.g. Equality or range on the leading key column). – Aaron Bertrand Dec 21 '16 at 14:06
  • 2
    @sp_BlitzErik Thanks for the info on Trace Flag never know that.. – Pரதீப் Dec 21 '16 at 16:45
  • 3
    Don't feel stupid, I learned something from the answers to your question. And I agree @sp_BlitzErik and/or AaronBertrand should post their answers. – SqlZim Dec 21 '16 at 17:34
  • 8
    I'll let @AaronBertrand post his as an answer. He could use the points. – Erik Darling Dec 21 '16 at 18:18
10

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.

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.