SQL Server Statistics

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.

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

Turn from:

http://blog.csdn.net/dba_huangzj/article/details/8041267

Http://blog.csdn.net/column/details/sqlserverdba.html

SQL Server Statistics