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.
.gif "Topic link icon")
Transact-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
Help and Information
Getting SQL Server 2005 Assistance
Change History
| Release | History |
|---|---|
5 December 2005 |
|