Database updating statistics
Lots of questions occur to people when I’m doing a presentation about statistics, and some get asked in front of the rest of the audience.Then there are other questions that get asked later on, in conversation. There is no essential difference between the statistics on an index and the statistics on a table.If you're like me, you have a SQL Agent job in place to rebuild or reorganize only the indexes in your databases that truly require such actions.If you rely on the standard maintenance plans in Microsoft SQL Server, a policy of rebuilding all indexes occurs.There are multiple paths a database can use to answer a query, some of them being faster and more efficient than others.It is the job of the query optimizer to evaluate and choose the best path, or execution plan, for a given query.That is whether such actions are required or not for a specific index, a rebuild of the index and all the locking and churning in the logs occurs.
Set the @Max Days Old variable to the number of days you will allow the statistics to be out of date by.
Try as I might, I find it hard to over-emphasize the importance of statistics to SQL Server.
Bad or missing statistics leads to poor choices by the optimizer: The result is horrific performance.
[cc lang=”sql”] EXEC sp_updatestats [/cc] The other way, is to use the UPDATE STATISTICS command.
It is one line of code that will update all the statistics in the database using the default sample size of 20,000 rows per table.