SQL Server Auto Update and Auto Create Statistics Options

By: Tibor Nagy | Updated: 2012-09-13 | Comments (9) | Related: > Indexing

Problem

I am new to SQL Server statistics. Could you please describe what are the benefits of using the Auto Create Statistics and Auto Update Statistics options? I am also curious about how these statistics are automatically created and updated. Check out this tip to learn more.

Solution

How do the statistics increase the performance of your SQL Server query execution? The statistical histograms are used by the Query Optimizer to choose the optimal query execution plan. If a query predicate contains a column with statistics, the Query Optimizer does not have to estimate the number of rows affected by that query, thus the Query Optimizer has enough information to create the execution plan. The SQL Server creates statistics in different ways:

The statistics are automatically created for each new index.
If the database setting AUTO_CREATE_STATISTICS is on, then the SQL Server will automatically create statistics for non-indexed columns that are used in your queries.

Auto Create Statistics Option

When you set the AUTO_CREATE_STATISTICS option on, the Query Optimizer creates statistics on individual columns used in a predicate, if these statistics are not already available. These statistics are necessary to generate the query plan. They are created on columns that do not have a histogram in an existing statistics object. The name of the auto-created statistics includes the column name and the object ID in hexadecimal format: _WA_Sys_<column_name>_<XXXX>. These statistics are used by the Query Optimizer to determine the optimal Query Execution Plan. The characteristics of these statistics objects are further discussed here.

You can enable the automatic statistics creation by running this SQL statement:

ALTER DATABASE YourDBName 
SET AUTO_CREATE_STATISTICS ON

Auto Update Statistics Option

Statistics are checked before query compilation or before executing a cached query plan. Statistics are considered out-of-date when:

There was a data change on an empty table.
The number of rows in the table was 500 or less at the time of statistics creation and the column modification counter of the leading column of the statistics object has changed by more than 500 since then.
The table had more than 500 rows when the statistics were gathered, and the column modification counter of the leading column of the statistics object has changed by more than 500 + 20% of the number of rows in the table when the statistics were gathered.
A table in TempDB with less than 6 rows had at least 6 row modifications.

More detailed information about automatic statistics maintenance is available in MSDN.

You can turn on automatic statistics update by running this SQL statement:

ALTER DATABASE YourDBName 
SET AUTO_UPDATE_STATISTICS ON

The outdated statistics can cause a lot of performance issues therefore it is recommended to enable it. The default option is ON. The usual symptoms of non-updated statistics are suboptimal query plans and degraded performance. Sometimes it is even worse to have outdated statistics than no statistics at all!

You can also set the database to update statistics asynchronously:

ALTER DATABASE YourDBName 
SET AUTO_UPDATE_STATISTICS_ASYNC ON

If you enable this option then the Query Optimizer will run the query first and update the outdated statistics afterwards. When you set this option to OFF, the Query Optimizer will update the outdated statistics before compiling the query. This option can be useful in OLTP environments while it can have negative effects in data warehouses. You can find additional details about this option in this tip.

How to turn off the SQL Sever Auto Update Statistics option?

In certain rare cases you might have to disable this useful feature. The auto update statistics feature can be turned off at different levels:

Use sp_autostats to display and change the auto update statistics setting for a table, index, or statistics object.
At the table level you can use the NORECOMPUTE option of the UPDATE STATISTICS command. This can be undone by running the UPDATE STATISTICS command without the NORECOMPUTE option.
You can also use the NORECOMPUTE option with CREATE STATISTICS command, but in that case you will have to drop and re-create the statistics if you would like to change this setting.
Use the STATISTICS_NORECOMPUTE option with the CREATE INDEX statement. You can re-enable the automatic statistics updates by running the ALTER INDEX command.
On the database level, you can disable auto update statistics by running this SQL statement:
```
ALTER DATABASE YourDBName 
SET AUTO_UPDATE_STATISTICS OFF
```

Please note that if you disable the auto update statistics option on the database level then there will be no statistics updates regardless of the individual table, index or statistics object settings.

When should you create SQL Server statistics?

One answer is to create statistics whenever the Database Engine Tuning Advisor suggests creating statistics. Another reason for creating statistics is that you can see missing statistics warnings (a yellow triangle with a black exclamation point or red table name in SQL Server 2000) on the Graphical Query Execution Plan as shown in the screenshot below.

SQL Server Missing Statistics Warning in a Query Plan

You can use SQL Server Profiler to monitor the Missing Column Statistics event class. You should also consider creating statistics when your query selects from a subset of data or the query predicate contains multiple correlated columns.

The statistics can be created by using the CREATE STATISTICS command:

--Create statistics on all rows
CREATE STATISTICS statistics_name
    ON YourDBName.YourSchema.YourTable (YourColumn1, YourColumn2)
    WITH FULLSCAN

--Create statistics using a random 10 percent sampling rate
CREATE STATISTICS statistics_name
    ON YourDBName.YourSchema.YourTable (YourColumn1, YourColumn2)
    WITH SAMPLE 10 PERCENT

When should you update SQL Server statistics?

If your queries are executing slower, then it is time to update the statistics. It is recommended to update your statistics after you insert a larger amount of data into ascending or descending key columns because in such cases the statistics histogram might not have any information about the newly inserted values. It is also highly recommended to update the statistics after maintenance except index maintenance (the data distribution does not change if you rebuild, defragment or reorganize an index).

At a minimum I generally recommend updating statistics daily if the data changes frequently in your database. Generally speaking, you can update that statistics less frequently with data warehouses storing historical data where the refreshes are weekly or monthly data loads. When I update statistics, I generally recommend running the sp_updatestats stored procedure as described in this tip.

Next Steps

Check out these related tips to learn more about SQL Server statistics and indexing:
- Performance Tuning Tips category articles
- Indexing Tips category articles
Read more tips by the author here.

About the author

Tibor Nagy is a SQL Server professional in the financial industry with experience in SQL 2000-2012, DB2 and MySQL.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2012-09-13

Tuesday, February 16, 2021 - 7:35:03 PM - Scott Ford	Back To Top (88248)
SELECT name, is_auto_update_stats_on, is_auto_update_stats_async_on FROM sys.databases

Saturday, August 26, 2017 - 8:14:29 AM - Greg Robidoux	Back To Top (65403)
Thanks this is fixed.

Wednesday, July 1, 2015 - 6:34:58 AM - RADHAKRISHNA	Back To Top (38090)
Thanks for mydoubt primary key constrcut field rows how to rename with database name

Thursday, September 13, 2012 - 9:51:32 AM - Gagandeep kaur	Back To Top (19504)
Thanks a lot Tibor, it actually cleared lots of my doubts about statistics !!