August 25, 2009 at 3:46 am
Is it
1) Periodically
2) When data changes
3) When the query runs
4) or all the above?
The BOL says:
When the AUTO_UPDATE_STATISTICS database option is set to ON (the default), the query optimizer automatically updates this statistical information periodically as the data in the tables changes.
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.
August 25, 2009 at 4:47 am
According to the article at http://technet.microsoft.com/en-us/library/cc966419.aspx#XSLTsection130121120120"> http://technet.microsoft.com/en-us/library/cc966419.aspx#XSLTsection130121120120 when one of the conditions is met, it marks the statistics as out of date:
1) The table size has gone from 0 to >0 rows.
2) The number of rows in the table when the statistics were gathered was 500 or less, and the colmodctr of the leading column of the statistics object has changed by more than 500 since then.
3) The table had more than 500 rows when the statistics were gathered, and the colmodctr of the leading column of the statistics object has changed by more than 500 + 20% of the number of rows in the table when the statistics were gathered.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
August 25, 2009 at 4:58 am
When a query runs, needs to use statistics and those statistics are out of date. For what makes stats out of date, see Adi's post.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 25, 2009 at 5:06 am
Thanks for the reply.
But my question is NOT, when the statistics becomes OUTdated.
My question is, When SQL Server engine UPdates the statistics?
August 25, 2009 at 6:37 am
Read my previous post.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply