Db property: Auto update statistics, isnt as good as a full statistics update as the sample size is small. Possibly a weekend job to do a full update of every index.
sp_updatestats will call update statistics on every table\index in the db but is smart enough to only call it if the index needs it as opposed to a forced full rebuild of everything. Uses rowmodctr column in sys.sysindexes to gauge the % of rows changed.
Leave auto update ON - kim tripp recommends. ANy shit with a particular one then leave on at db level and off for that particular one.
2005 introduced "Aynch Auto Update" - if an out of date stat is found an async update stats will be triggered but the currently executing query optimised job does not wait for it.
When do they get invalidated?
This
not when they get updated.
Permanent table
If the table has no rows, statistics is updated when there is a single change in table.
If the number of rows in a table is less than 500, statistics is updated for every 500 changes in table.
If the number of rows in table is more than 500, statistics is updated for every 500+20% of rows changes in table.
Temporary table
If the table has no rows, statistics is updated when there is a single change in table.
If the number of rows in table is less than 6, statistics is updated for every 6 changes in table.
If the number of rows in table is less than 500, statistics is updated for every 500 changes in table.
If the number of rows in table is more than 500, statistics is updated for every 500+20% of rows changes in table.
Table variable
There is no statistics for Table Variables.
When do they get updated after invalidation?
- If auto-update-stats is enabled, the first plan that compiles and sees the invalid stats will update them ther and then.
- If auto-update-stats-async is enabled, the first plan that compiles and see the invalid stats will use the invalid stats to compile and will cause an entry to be put on a task queue for a background task to update the stats.
- If neither is on, they won’t be updated until someone does an sp_updatestats or UPDATE STATISTICS.