Share via


DBCC SHOW_STATISTICS (Transact-SQL)

Displays the current distribution statistics for the specified target on the specified table.

Important

This feature has changed from earlier versions of SQL Server. For more information, see Behavior Changes to Database Engine Features in SQL Server 2005.

Topic link iconTransact-SQL Syntax Conventions

Syntax

DBCC SHOW_STATISTICS ( table_name | view_name , target ) 
[ WITH [ NO_INFOMSGS ] < option > [ , n ] ]
< option > :: =
    STAT_HEADER | DENSITY_VECTOR | HISTOGRAM

Arguments

  • table_name | view_name**
    Is the name of the table or indexed view for which to display statistics information. Table and view names must comply with the rules for identifiers.
  • target
    Is the name of the object (index name, statistics name or column name) for which to display statistics information. Target names must comply with the rules for identifiers. If target is a name of an existing index or statistics on a table then the statistics information about this target is returned. If target is the name of an existing column, and an automatically created statistic on this column exists, and then information about that auto-created statistic is returned.
  • NO_INFOMSGS
    Suppresses all informational messages that have severity levels from 0 through 10.
  • STAT_HEADER | DENSITY_VECTOR | HISTOGRAM [ **,**n ]
    Specifying one or more of these options limits the result sets returned by the statement to the specified option. If no options are specified, all statistics information is returned.

Result Sets

The following table describes the columns returned in the result set when STAT_HEADER is specified.

Column name Description

Name

Name of the statistic.

Updated

Date and time the statistics were last updated.

Rows

Number of rows in the table.

Rows Sampled

Number of rows sampled for statistics information.

Steps

Number of distribution steps.

Density

Selectivity of the first index column prefix excluding the EQ_ROWS, which are described in the section about the HISTOGRAM option result set.

Average key length

Average length of all the index columns.

String Index

Yes indicates that the statistics contain a string summary index to support estimation of result set sizes for LIKE conditions. Applies only to leading columns of char, varchar, nchar, and nvarchar, varchar(max), nvarchar(max), text, and ntext data types.

The following table describes the columns returned in the result set when DENSITY_VECTOR is specified.

Column name Description

All density

Selectivity of a set of index column prefixes including the EQ_ROWS, which are described in the section about the HISTOGRAM option result set.

Average length

Average length of a set of index column prefixes.

Columns

Names of index column prefixes for which All density and Average length are displayed.

The following table describes the columns returned in the result set when the HISTOGRAM option is specified.

Column name Description

RANGE_HI_KEY

Upper bound value of a histogram step.

RANGE_ROWS

Estimated number of rows from the table that fall within a histogram step, excluding the upper bound.

EQ_ROWS

Estimated number of rows from the table that are equal in value to the upper bound of the histogram step.

DISTINCT_RANGE_ROWS

Estimated number of distinct values within a histogram step, excluding the upper bound.

AVG_RANGE_ROWS

Average number of duplicate values within a histogram step, excluding the upper bound (RANGE_ROWS / DISTINCT_RANGE_ROWS for DISTINCT_RANGE_ROWS > 0).

Remarks

The results returned indicate the selectivity of an index (the lower the density returned, the more selective the index is) and provide the basis for determining whether an index is useful to the query optimizer. The results returned are based on distribution steps of the index.

To see the last date the statistics were updated, use the STATS_DATE function.

Permissions

User must own the table, or be a member of the sysadmin fixed server role, the db_owner fixed database role, or the db_ddladmin fixed database role.

Examples

A. Returning all statistics information

The following example displays all statistics information for the AK_Product_Name index of the Person.Address table.

USE AdventureWorks;
GO
DBCC SHOW_STATISTICS ("Person.Address", AK_Address_rowguid);
GO

B. Specifying the HISTROGRAM option

The following example limits the statistics information displayed for the AK_Product_Name index to the HISTOGRAM data.

USE AdventureWorks;
GO
DBCC SHOW_STATISTICS ("Person.Address", AK_Address_rowguid) WITH HISTOGRAM;
GO

See Also

Reference

CREATE INDEX (Transact-SQL)
CREATE STATISTICS (Transact-SQL)
DBCC (Transact-SQL)
DROP STATISTICS (Transact-SQL)
sp_autostats (Transact-SQL)
sp_createstats (Transact-SQL)
STATS_DATE (Transact-SQL)
UPDATE STATISTICS (Transact-SQL)
USE (Transact-SQL)

Other Resources

Index Statistics

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release History

5 December 2005

Changed content:
  • Corrected the description of the Density column for the result set produced by the STAT_HEADER option.
  • Corrected the description of the All density column for the result set produced by the DENSITY_VECTOR option.