DBCC SHOW_STATISTICS

Transact-SQL Reference

Transact-SQL Reference

DBCC SHOW_STATISTICS

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

Syntax

DBCC SHOW_STATISTICS ( table , target )

Arguments

table

Is the name of the table for which to display statistics information. Table names must conform to the rules for identifiers. For more information, see Using Identifiers.

target

Is the name of the object (index name or collection) for which to display statistics information. Target names must conform to the rules for identifiers. If target is both an index name and a statistics collection name, both index and column statistics are returned. If no index or statistics collection is found with the specified name, an error is returned.

Remarks

The results returned indicate the selectivity of an index (the lower the density returned, the higher the selectivity) and provide the basis for determining whether or not 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 STATS_DATE.

Result Sets

This table describes the columns in the result set.

Column name Description
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 (non-frequent).
Average key length Average length of the first index column prefix.
All density Selectivity of a set of index column prefixes (frequent).
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.
RANGE_HI_KEY Upper bound value of a histogram step.
RANGE_ROWS Number of rows from the sample that fall within a histogram step, excluding the upper bound.
EQ_ROWS Number of rows from the sample that are equal in value to the upper bound of the histogram step.
DISTINCT_RANGE_ROWS 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).
Permissions

DBCC SHOW_STATISTICS permissions default to members of the sysadmin fixed server role, the db_owner and db_ddladmin fixed database role, and the table owner, and are not transferable.

Examples

This example displays statistics information for the UPKCL_auidind index of the authors table.

USE pubs
DBCC SHOW_STATISTICS (authors, UPKCL_auidind)
GO

Here is the result set:

Statistics for INDEX 'UPKCL_auidind'.
Updated               Rows   Rows Sampled   Steps   Density
--------------------- ------ -------------- ------- --------------
Mar  1 2000  4:58AM    23    23             23      4.3478262E-2

Average key length
------------------
11.0

(1 row(s) affected)

All density              Average Length           Columns                                                                                                                                                                                                                                                          
------------------------ ------------------------ ---------------- 
4.3478262E-2             11.0                     au_id

(1 row(s) affected)

RANGE_HI_KEY RANGE_ROWS EQ_ROWS DISTINCT_RANGE_ROWS AVG_RANGE_ROWS           
------------ ---------- ------- ------------------- --------------
172-32-1176  0.0        1.0     0                   0.0
213-46-8915  0.0        1.0     0                   0.0
238-95-7766  0.0        1.0     0                   0.0
267-41-2394  0.0        1.0     0                   0.0
274-80-9391  0.0        1.0     0                   0.0
341-22-1782  0.0        1.0     0                   0.0
409-56-7008  0.0        1.0     0                   0.0
427-17-2319  0.0        1.0     0                   0.0
472-27-2349  0.0        1.0     0                   0.0
486-29-1786  0.0        1.0     0                   0.0
527-72-3246  0.0        1.0     0                   0.0
648-92-1872  0.0        1.0     0                   0.0
672-71-3249  0.0        1.0     0                   0.0
712-45-1867  0.0        1.0     0                   0.0
722-51-5454  0.0        1.0     0                   0.0
724-08-9931  0.0        1.0     0                   0.0
724-80-9391  0.0        1.0     0                   0.0
756-30-7391  0.0        1.0     0                   0.0
807-91-6654  0.0        1.0     0                   0.0
846-92-7186  0.0        1.0     0                   0.0
893-72-1158  0.0        1.0     0                   0.0
899-46-2035  0.0        1.0     0                   0.0
998-72-3567  0.0        1.0     0                   0.0

(23 row(s) affected)

See Also

CREATE INDEX

CREATE STATISTICS

DBCC

Distribution Statistics

DROP STATISTICS

sp_autostats

sp_createstats

sp_dboption

STATS_DATE

UPDATE STATISTICS

USE