Typically, SQL Server itself will automatically update an index’s statistics when 20% of the index’s data changes.
While this may be sufficient some times, there are many cases where it is not.
In that article I provided SQL code that showed when the statistics were last updated and how much the underlying data had changed since the last update.
Here I go one step further and provide SQL code that will update the statistics, and do so in an intelligent manner.
In addition you’ll want to turn on the “Auto Update Statistics Asynchronously” setting for the database or databases which hold the super large table.When you’ve got very large tables that then trigger auto stats to run if it takes more than 30 seconds for the update stats command to run the query that triggered the auto stats to time out, which causes that transaction to roll back, which means that the auto stats command will also roll back.So the next query will then fire the auto stats update and the process will repeat over and over.My previous post on the Ascending Key problem demonstrated a common issue where the first of these scenarios could be affecting you.We’ll look at examples of the other scenarios in subsequent posts.