Performance optimization-Statistics--sqlserver Automatic Update and auto-create statistics options (reprint)

Original translation from: Http://www.mssqltips.com/sqlservertip/2766/sql-server-auto-update-and-auto-create-statistics-options/?utm _source=dailynewsletter&utm_medium=email&utm_content=headline&utm_campaign=2012913

How does statistical information improve SQL Server query performance? The statistical histogram is used as the basis for selecting the query optimizer in the query execution plan. If a query predicate contains a column of statistics, the query optimizer does not need to predict the number of rows affected in the query, so the query optimizer has enough information to create the execution plan. SQL Server creates an execution plan in several different ways:

• Statistics are automatically created in each newly created index.
• If Auto_create_statistics is set to On,sqlserver in the database, the statistics are automatically created for the columns that are used in the query and that do not have an index.

Auto_create_statistics options:

When this option is set to ON, the query optimizer will create statistics for each column independently of the columns used in the predicate, if the statistics for those columns are not available. These statistics are necessary to create a query plan. They are created on columns that do not have histograms in existing statistical objects, including the hexadecimal format:_wa_sys_<column_name>_<xxxx> for column names and object IDs. These statistics are used by the query optimizer to decide which optimized execution plan to use.

You can enable the automatic statistics creation feature by using the following statement:

ALTER database[your library name] SET Auto_create_statistics on

Auto Update Statistics Options:

Statistics are checked before the query compiles or executes the cache execution plan. Statistics are considered to expire when the following conditions occur:

1. There are data changes in an empty table.

2. When statistics are created, the number of rows in the table is only 500 or below, and the number of boot columns in the statistics object is changed more than 500.

3, when the statistical information of the table is collected, more than 500 rows, and the number of the Guide column of the statistical object later changed more than the number of the head of the 20%.

4. Tables in tempdb, less than 6 rows, and at least 6 rows are changed.

More information can be seen on MSDN

You can use the statement to turn on Automatic Update statistics:

ALTER database[your library name] SET Auto_update_statistics on

Outdated statistics can cause a lot of performance problems, so it's recommended to turn on Automatic Updates. Its default setting is on. The common impact of not updating statistics is that suboptimal execution plans are selected and performance is degraded. Sometimes, out-of-date statistics can be worse than no statistical information.

Use the following statement to turn on asynchronous update statistics:

ALTER database[your library name] SET Auto_update_statistics_async on

If this option is turned on, the query optimizer executes the query first and then updates the outdated statistics. When you set this option to OFF, the query optimizer updates the expiration statistics before compiling the query. This option is useful in OLTP environments, but has a negative impact in the data warehouse.

How do I turn off the option for SQL Server to automatically update statistics?

In very special cases, you have to disable this useful feature and you can turn it off in the following way:

1. Use sp_autostats to explicitly and change the auto-Update STATISTICS option on a table, index, or statistic object.

2. At the table level, you can use the norecomputeoption of the UPDATE statistics command.

3. You can also use the NoRecompute option in the Createstatistics command, but then you need to delete and rebuild the statistics.

4. Use Statistics_norecompute in the CREATE INDEX command.

5. At the database level, you can use the following command to disable:

ALTER database[your library name] SET Auto_update_statistics OFF

When you use database-level disabling, the settings for tables, indexes, or statistics objects are all invalidated.

When do I create statistics?

One answer is that it is recommended to create when using Database Engine Tuning Advisor (DTA). Another situation is when you view the execution plan is a warning that a missing statistic appears (missing statistics warnings), such as the Yellow triangle exclamation mark:

You can use SQL Server Profiler to monitor events that have lost column statistics, and you can also consider creating statistics on those columns where your query contains associated columns from a subset or query predicate.

The statements that create the statistics are as follows:

--create statistics on all rows CREATE statisticsstatistics_name ONYourDBName.YourSchema.YourTable (YOURCOLUMN1,YOURCOLUMN2) With FULLSCAN --create statistics using a random percent sampling rate CREATE statisticsstatistics_name ONYourDBName.YourSchema.YourTable (YOURCOLUMN1,YOURCOLUMN2) With SAMPLE 10PERCENT

When do I update statistics?

If your query executes slowly, it's time to update the statistics. It is also recommended to update STATISTICS when you insert large amounts of data into ascending or descending columns, because in this case the histogram of statistics does not contain the newly inserted values, and it is strongly recommended that the statistics be updated after maintenance work except for index maintenance (when you rebuild, defragment, or reorganize the index, the data distribution does not change).

If the data changes frequently in the database, it is recommended that you update the statistics at least once a day. In general, in a data warehouse, the frequency of updating statistics can be reduced, and when updated, it is generally recommended that sp_updatestats stored procedures be implemented.