Low a sampling rate for Update Statistics can have the effect of causing the optimizer to choose a bad plan that would lead to an inaccurate creation of the statistics. Changing to Update Statistics with Full Scan solves the issue.
However, full scan statistics update can be slow and expensive on a large system, and a [...]
What neither SQL Server 2000 nor SQL Server 2005 solve yet is to manage outdated or obsolete statistics on tiny tables (in terms of number of rows).
This is much easier to handle with huge tables. If you add one row to a 1-million row table it will very likely not matter regarding the data distribution. [...]
I’d recommend the following:
1) Turn autoupdates off (they cause perf overhead) and opening up the Fill Factor in the index from 80% (default) to 50% or lower, which will leave more room to grow the index.
2) Schedule autoupdate statistics runs cyclicly as needed.
Another reason to turn off Auto Update Statistics
We have found (beyond a reasonable doubt - even at a certain company’s internal SQLServer databases) that statistics for SQLServer seem to get corrupt from the optimizers point of view over time.
One theory is that auto stats may contribute to that. It’s gotten so bad on some of [...]
One way to determine how often that AUTOSTATS runs in SQL runs is to use the trace flag 8721. When turned on, it outputs to the errorlog every time AUTOSTATS runs. This information can be used to help determine if AUTOSTATS is running too often or not. Be sure to turn off this trace flag [...]
One way to tell if the statistics for a column are missing or outdated, or if the accuracy of current statistics is not ideal (they may not be ideal if the sample size of the statistics are not high enough), is to run a graphical execution plan of a query as an estimated plan and [...]
According to a SQL Server tuning expert at Microsoft (one of their best), it was suggested to me that if your SQL Server maintenance window allows for it, that you should update statistics for all tables and for all databases every night.
This is because the auto-update option is less than perfect, and accurate and up-to-date [...]
If you have a performance issue with a query, the very first step you should take before analyzing it is to update the statistics on all the tables in your database.
This is because many query performance issues are due to outdated statistics, and updating them before troubleshooting query performance may save you a lot of [...]
If you want to find out if an index has had its indexes updated or not, you can use the DBCC SHOW_STATISTICS command, like this:
DBCC SHOW_STATISTICS (table_name , index_name)
This command will not only tell you when statistics were last updated, but also provide you information on the kind of statistics that has been collected for [...]
Statistics are only maintained on the first column of a composite index. Because of this, SQL Server’s Query Optimizer might not make as good use of composite indexes as it could. If you suspect that a composite index is not being used as you expected it would be, you will want first to verify that [...]