Check the current statistics for table in SQL Server
Displays the current distribution statistics for the specified target on the specified table.
Syntax
DBCC SHOW_STATISTICS ( table , target )
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).
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)