DBCC SHOW_STATISTICS (T-SQL)

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 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

DBCC SHOW_STATISTICS returns this result set; the result set shown is for authors table and aunmind index (values may vary):

Statistics for INDEX 'aunmind'.

Updated Rows Rows Sampled Steps Density

-------------------- ----------- ------------ ----------- --------------

May 13 1998 4:35PM 23 23 23 3.9697543E-2

Average key length

---------- ------------------------

22.173912

(1 row(s) affected)

All density Columns

------------------------ -----------------------------------------------

4.5454547E-2 au_lname

4.3478262E-2 au_lname, au_fname

(2 row(s) affected)

Steps

----------------------------------------

Bennet

Blotchet-Halls

Carson

DeFrance

del Castillo

Dull

Green

Greene

Gringlesby

Hunter

Karsen

Locksley

MacFeather

McBadden

O'Leary

Panteley

Ringer

Smith

Straight

Stringer

White

Yokomoto

(23 row(s) affected)

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

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 index
Average key length	Average length of a row
All density	Selectivity of the specified column prefix in the index
Columns	Name of the index column prefix for which the all density is displayed
Steps	Number of histogram values in the current distribution statistics for the specified target on the specified table

Permissions

DBCC SHOW_STATISTICS permissions default to members of the sysadmin fixed server role or the db_owner fixed database role, or 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

-------------------- ----------- ------------ ----------- --------------

Feb 23 1998 9:04AM 23 23 23 4.3478262E-2

Average row length

------------------------

88.695663

(1 row(s) affected)

All density Columns

------------------------ -----------------------------------------------

4.3478262E-2 au_id

(1 row(s) affected)

Steps

-----------

172-32-1176

213-46-8915

238-95-7766

267-41-2394

274-80-9391

341-22-1782

409-56-7008

427-17-2319

472-27-2349

486-29-1786

527-72-3246

648-92-1872

672-71-3249

712-45-1867

722-51-5454

724-08-9931

724-80-9391

756-30-7391

807-91-6654

846-92-7186

893-72-1158

899-46-2035

998-72-3567

(23 row(s) affected)

CREATE INDEX	sp_dboption
CREATE STATISTICS	STATS_DATE
Distribution Statistics	UPDATE STATISTICS
DROP STATISTICS	USE
sp_autostats	DBCC
sp_createstats