SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

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);

  AS (SELECT TOP (499)
)) AS n
  FROM master.sys.all_columns AS ac
CROSS JOIN master.sys.all_columns AS ac2
INSERT INTO dbo.StatsTest
FROM Nums;

--auto create stats
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:

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.

The Scary DBA

I have twenty+ years experience in IT. That time was spent in technical support, development and database administration. I work forRed Gate Software as a Product Evangelist. I write articles for publication at SQL Server Central, Simple-Talk, PASS Book Reviews and SQL Server Standard. I have published two books, ”Understanding SQL Server Execution Plans” and “SQL Server 2008 Query Performance Tuning Distilled.” I’m one of the founding officers of the Southern New England SQL Server Users Group and its current president. I also work on part-time, short-term, off-site consulting contracts. In 2009 and 2010 I was awarded as a Microsoft SQL Server MVP. In the past I’ve been called rough, intimidating and scary. To which I usually reply, “Good.” You can contact me through grant -at- scarydba dot kom (unobfuscate as necessary).


Leave a comment on the original post [www.scarydba.com, opens in a new window]

Loading comments...