March 25, 2010 at 11:18 am
TheSQLGuru (3/25/2010)
Can you please provide a reference for that statement? Thanks!
Well I said it - reference enough! 😀 :laugh:
Ok, ok:
TechNet: Statistics used by the Query Optimizer
A relevant extract:
After a series of INSERTs, DELETEs, and/or UPDATEs are performed on a table, the statistics may not reflect the true data distribution in a given column or index. If the SQL Server query optimizer requires statistics for a particular column in a table that has undergone substantial update activity since the last time the statistics were created or updated, SQL Server automatically updates the statistics by sampling the column values (using auto update statistics). The statistics auto update is triggered by query optimization or by execution of a compiled plan, and involves only a subset of the columns referred to in the query. Statistics are updated before query compilation if AUTO_UPDATE_STATISTCS_ASYNC is OFF, and asynchronously if it is ON.
When a query is first compiled, if the optimizer needs a particular statistics object, and that statistics object exists, the statistics object is updated if it is out of date. When a query is executed and its plan is in the cache, the statistics the plan depends on are checked to see if they are out of date. If so, the plan is removed from the cache and during re-compilation of the query the statistics are updated. The plan also is removed from the cache if any of the statistics it depends on have changed.
[/color]
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 25, 2010 at 11:37 am
Thanks Paul.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
March 25, 2010 at 11:42 am
TheSQLGuru (3/25/2010)
Thanks Paul.
No worries. I was a little concerned that I had missed your point - and I should not have said 'never' updated...there's always an exception, even if its just someone running sp_updatestats!
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply