DBCC SHOW STATISTICS in Microsoft SQL Server

DBCC SHOW_STATISTICS

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)

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s