SQL SERVER Statistics Overview (Statistics)

Objective

The query optimizer uses statistics to create query plans that improve query performance, and for most queries, the query optimizer has generated the necessary statistics for a high-quality query plan, but in rare cases you need to create additional statistics or modify the query design to get the best results. Therefore, understanding and rational use of statistical information is one of the ways of database optimization.

Classification of statistical information

Depending on the source you create, statistics are divided into two table statistics and index statistics, and unless you customize them, there is no essential difference between them.

Index statistics are built on the index, so for creating data that already exists, all data is scanned when the index is created, and the data is also created on the index's statistics.

The statistics for the table are created above the columns. For data tables that already exist, you need to use a filtering method (such as where,join-on) for some columns, which automatically establishes the statistics above. It's just that they use sampled data instead of all the data.

Storage of statistical information

Statistics are stored in a series of system tables, such as Sysindex, where you can view some of the information through the system view sys.stats and Sys.index. However, detailed information can be viewed through the system function DBCC SHOW_STATISTICS. The statistics themselves occupy little disk space, so there is no need to focus on deleting unnecessary statistics to free up more disk space.

How to view statistics update time

DBCC Show_statistics

Grammar

DBCC show_statistics (table_or_indexed_view_name, target) [with [NO_INFOMSGS] < option > [, n]]< option >:: = Stat_header | Density_vector | Histogram | Stats_stream

Parameters

Table_or_indexed_view_name

The name of the table or indexed view whose statistics you want to display.

Target

The name of the index, statistic, or column whose statistics you want to display. If Target is the name of an existing index or statistic for a table or indexed view, the statistics about this target are returned. If Target is the name of an existing column and there are auto-created statistics in this column, information about that automatically created statistic is returned. If the automatically created statistics do not exist in the column target, error message 2767 is returned.

No_infomsgs

Cancels all informational messages for severity levels from 0 to 10.

Stat_header | Density_vector | Histogram | Stats_stream [ ,N]

If you specify one or more of these options, the result set returned by the statement is restricted according to the specified options. If no option is specified, all statistics are returned.

Instance

The following example displays all statistics for the Ak_address_rowguid index of the person.address table in the ADVENTUREWORKS2012 database

DBCC show_statistics ("Person.Address", Ak_address_rowguid);

The following example restricts the statistics displayed for the Ak_address_rowguid index to histogram data.

DBCC show_statistics ("Person.Address", ak_address_rowguid) with histogram;

When to maintain statistical information

• Query execution time is long
• Insert operation occurs on ascending or descending key column
• Truncate a table or BULK insert on most columns of a table

How to maintain statistical information

SQL Server provides two basic commands to update statistics

• Sp_updatestats
• UPDATE STATISTICS

The differences and how to use these 2 commands are described in a subsequent article.

SQL SERVER Statistics Overview (Statistics)