Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

Free SQL Server Webcast > Building Really Fast SQL Server VMs
 

SQL Server Auto Update and Auto Create Statistics Options


By:   |   Read Comments (8)   |   Related Tips: More > 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


Last Update:


next webcast button


next tip button



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.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Sunday, October 08, 2017 - 6:36:15 PM - Adi Back To Top

 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?

Cheers,

Adi


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

Thanks this is fixed.


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

 Hi,

 

Please update this statement

 

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

correction

CREATE STATISTICS

 

thanks

Ras

 


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

 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?

Thanks

 


Friday, December 04, 2015 - 4:14:34 AM - Tibor Nagy Back To Top

Hi Abdo,

 

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

 

Regards,

Tibor


Thursday, December 03, 2015 - 5:15:24 AM - Abdoali Back To Top

Hi,

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

Abdo.


Wednesday, July 01, 2015 - 6:34:58 AM - RADHAKRISHNA Back To Top

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

Thanks a lot Tibor, it actually cleared lots of my doubts about statistics !!


Learn more about SQL Server tools