http://www.sqlservercentral.com/blogs/scarydba/2013/01/07/statistics-update-clarification/

Printed 2014/07/28 04:42PM

Statistics Update Clarification

By Grant Fritchey, 2013/01/07

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

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:

Stats1

 

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.


Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.