Statistics Update Clarification

By default statistics are created automatically within SQL Server. And, by default, these stats are updated automatically based on a set of triggers. The triggers are defined as

• If 0 rows – Any data added leads to a statistics update
• If < 500 rows – 500 rows added causes a stats update
• If > 500 rows – 500 rows + 20% of the number of rows causes a stats update (unless you enable a traceflag in 2012 in which case you get a proportional value instead of 20%).

There are some exceptions for temporary tables and some variations for filtered statistics and filtered indexes, but you get the idea. I was writing an article on statistics in preparation for another Oracle/SQL Server discussion (on, you guessed it, statistics) and I came to these rules and wrote something along the lines of “So even if you have 499 rows, in order to see the stats update you would need to have to add more than 500 additional rows, meaning you would get to 999 before you saw a statistics update.” Which made sense, but I was suddenly concerned, is that valid. I decided to clarify it for my own piece of mind.

Quick set up of a test table and loading 499 rows into followed. I also ran a simple query to generate some statistics on the column:

CREATE TABLE dbo.StatsTest (StatsColumn INT);

WITH Nums
  AS (SELECT TOP (499)
ROW_NUMBER() OVER (ORDER BY (SELECT 1
)) AS n
  FROM master.sys.all_columns AS ac
CROSS JOIN master.sys.all_columns AS ac2
)
INSERT INTO dbo.StatsTest
(StatsColumn)
SELECT n
FROM Nums;

--auto create stats
SELECT *
FROM dbo.StatsTest AS st
WHERE StatsColumn = 42;

This results in the follow set of statistics:

If you click on it to look at the details you can see that it shows 499 rows sampled.

If I add two more rows to the statistics and then run a different query, guaranteed to require a statistics update:

SELECT	*
FROM	dbo.StatsTest AS st
WHERE	StatsColumn < 42;

If I rerun DBCC SHOW_STATISTICS I still see the same output. In short, it appears that my original assumption was correct.

I ran several more tests, and I could get to 999 without updating the statistics, but adding just one more row and breaking 500 additional rows and the statistics update fired. I needed to validate this behavior for myself and it worked.