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