Today we are going to unpack some details wrapped around things you would want to know when building a Database Maintenance Task for statistics using the Database Maintenance Task Wizard.
As a background regarding statistics, it is important for you to know that Statistics are used by the SQL Server Query Analyzer to determine the best execution method out of the many possible options. By default, an index will have statistics. Additionally, columns in tables or materialized views may also be set to maintain statistics.
Statistics are very important for determining a query plan. Statistics contain information regarding the data distribution within the object for which a statistic has been defined. As you modify data in a table, the statistics may no longer accurately reflect the contents. When this is the case, using stale statistics can result in a sub-optimal query plan from the Query Analyze. So, it is important to keep your statistics up to date. On a small database, stale statistics can have a bigger impact than a larger database.
SQL Server has the ability to automatically determine to create statistics based on your queries. If you put a column in a where clause that does not have a statistic, SQL Server will want to automatically target that column for managing statistics. Like all things SQL Server, the more indexes or statistics you have on a table, the longer it takes to make changes to the records in that table. Modifying data requires that every index be modified. If you have you database configured to automatically update statistics, then every column with a statistic assigned will have to have its statistics updated.
For this reason, when you have a very active database, SQL Server has the ability to allow you to manually create and/or update your statistics. You can move this work to low activity periods on your database, and perform them in a batch method when users are less active.
When statistics are updated, the engine reviews the contents of the table, and updates the statistics information. If it is a big table, this can take a very long time to complete. So, they have the ability for you to determine the percentage of the table to review when calculating the statistics. Often, on a very large table with millions of rows, this is the best plan, and is quite reasonable.
Out of all the maintenance tasks you can perform on your database, updating statistics, along with defragmenting your indexes, can have the biggest impact on your query performance, regardless of how finely you have tuned your queries. This is the foundation for database performance.
In ordfer to take advantage of updating statistics off peak times you must configure your database to not automatically update the statistics.
Cheers,
Ben