AUTO_UPDATE_STATISTICS fails to update stats for certain indexes. Why?

In the system I'm working on, we were seeing a particular SELECT use a clustered index scan, instead of using the index which was created for this SELECT. I took a look at the statistics for the index, and found that they were 26 days old. I updated the statistics with FULLSCAN. Now the SELECT is using the index.

AUTO_UPDATE_STATISTICS has been enabled in this database the entire time. Why did the statistics not get updated?

Looking at the STATS_DATE() output (query below):

SELECT 
    object_name = Object_Name(ind.object_id),
    IndexName = ind.name,
    StatisticsDate = STATS_DATE(ind.object_id, ind.index_id),
    ind.type
  FROM SYS.INDEXES ind
  order by STATS_DATE(ind.object_id, ind.index_id) desc

I see that many indexes were updated recently. Some large tables which have data updates every day have not had updated statistics in weeks.

How does MSSQL 2005 decide when to upda开发者_StackOverflow社区te statistics when AUTO_UPDATE_STATISTICS is enabled? The documentation states:

A statistics update is initiated whenever the statistics used in a query execution plan fail a test for current statistics. . . Almost always, statistical information is updated when approximately 20 percent of the data rows has changed.

Can this be trusted? Perhaps these tables are so large that the "current statistics test" is passing even though records are being inserted on a daily basis.

---

Here's a link to the algorithm details in a Microsoft whitepaper

http://technet.microsoft.com/sv-se/library/cc966419(en-us).aspx#XSLTsection130121120120

And this quoted from Erland Sommarskog:

When you have an empty table, autostats first kicks in after 500 rows. From there, autostats sets in when 20% of the rows have been modified. This is measure from some rowmodctr.

This has the effect that big tables with a monotonously growing key where queries goes against the trunk have their statistics updated far too rarely.

Whereas small tables where all rows are updated frequently, or where or rows are added and deleted, have their stats updated very often, which may cause recompilation issues.

---

The statistics are updated at 500 + 20% of the number of rows. This can take a long time some situations so it's sometimes better to do this manually.

for more info look here: http://www.sqlserveroptimizer.com/2013/02/how-to-make-your-sql-server-index-faster-by-updating-statistics/