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)