SQL Server Auto Update and Auto Create Statistics Options

By:   |   Comments (9)   |   Related: > Indexing


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.


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:


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:


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:


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:

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)

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

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

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Tibor Nagy 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

Comments For This Article

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

Sunday, October 8, 2017 - 6:36:15 PM - Adi Back To Top (67056)

 Hi. Thanks for the post. its a lot helpful. However, i need a specific solution for my test, dev and prod environment where i need to check if auto_create_statistics is set to OFF/ON against each DB and if its OFF- alter it to ON.

How can i achieve this?



Saturday, August 26, 2017 - 8:14:29 AM - Greg Robidoux Back To Top (65403)

Thanks this is fixed.

Friday, August 25, 2017 - 8:37:18 PM - Ras Back To Top (65346)



Please update this statement


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







Thursday, July 14, 2016 - 10:44:09 AM - Stuart Back To Top (41890)

 Hi Tibor,

Thanks for the information.

Can you just clarify something form me?

At the end of this page you say "At a minimum I generally recommend updating statistics daily...".

Presumably, this is only if you don't have auto update statistics set to on?



Friday, December 4, 2015 - 4:14:34 AM - Tibor Nagy Back To Top (40197)

Hi Abdo,


You can use STATS_DATE to find out the last stats update date as described in this tip.




Thursday, December 3, 2015 - 5:15:24 AM - Abdoali Back To Top (40189)


How to get creation date of stats that was created by auto-stats-creation.


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 !!

get free sql tips
agree to terms