SQL Server 2005 Usage of statistical information

1, what is statistical information

The following is an official description of the statistics:

By default, if a column in the table does not have an index, SQL Server automatically creates statistics for that column. The query optimizer then evaluates the statistics for the distribution of data in the column to select a more efficient query processing scheme. It is simple to distinguish the statistics that are created automatically, in SQL Server 7.0 and SQL Server 2000, the automatically created statistics are prefixed with _wa_sys.

Personal Impressions:

Previous statistics on the beginning of _wa_sys have been unaware of the use of the CBO when learning Oracle and three algorithms (nested loops, merge connections and hash connections) only to find that the statistical information of the beginning of the _wa_sys and the statistics of the index is useless to our users, the database is very important.

2, Statistics content: DBCC SHOW_STATISTICS (' [[[zping.com]]] ', idx_operator)

After running the above command, come out the following information:

Name Updated rows Rows Sampled Steps Density Average key length St Ring Index
Idx_operator 8:15PM 721370 721370 v 0.002035875 63.99907 YES

Here's a message stating:

Name: The names of the statistics. Updated: Date and time when statistics were last updated

Rows: The number of rows in the table. Rows Sampled: The number of sampled rows for statistics.

Steps: Number of distribution steps (up to 200 steps in SQL Server) Average key length: Average lengths of all indexed columns

This information is a summary of statistical information

The following information is also available:

All density Average Length Columns
0.004219409 31.99907 operator

All density: Optional (frequent) Average length: The average length of the index column prefix set. Columns: Name of index column prefix

There is also histogram information that includes data statistics:

3, the role of statistical information:

1, after index is established, whether the optimizer uses the index, the optimizer needs some statistical information to make judgments

2, according to the statistics, it is estimated that a nested loop connection, a merge connection, a hash connection, and so on which connection

3, according to the statistical information to determine the best estimate of the cost (the best order of execution),

4, statistical information automatically established:

1, when the index is established, a statistic with the same name appears

2, a column does not have statistics, then use him to correlate the table and query data, at this time, the system will evaluate the best query plan, generate a column of "_wa_sys" statistics.

Such as: This contains (index statistics and column statistics)

Statistical information is updated and maintained, and statistics are automatically maintained by SQL Server 2005 due to the constant change in data. Statistics can be updated manually:

Update STATISTICS [[zping.com]] (_wa_sys_creater_4c02db92) with Fullscan

Update the statistics for the table and collect all the data for that column in the table. It is generally recommended to use all data collection. Ensure the correctness of statistical information

5, the setting of statistical information

How is the system set up to update and create statistical information?

Originally, when the database was created, the system automatically set the statistics properties. These two properties are generally not manually altered.

SQL Server 2005 Usage of statistical information