THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Kalen Delaney

Geek City: String Statistics

If you’ve ever run DBCC SHOW_STATISTICS,  you know you get 3 sections of information back. The first section is basic information about the last time the statistics were updated, the number of rows, the number of steps, etc. The second section is density information for each left-based subset of columns. The third section is the histogram for the first column in the statistics. I won't be going into detail on what any of these things mean (i.e. steps, density, histogram), but you can get lots more information from this whitepaper:

Statistics Used by the Query Optimizer in Microsoft SQL Server 2005

What you might not know is that you can get each of the three sections independently by adding an option to the DBCC command:

DBCC SHOW_STATISTICS ('AdventureWorks.Person.Contact',
'IX_Contact_EmailAddress') WITH STAT_HEADER;

DBCC SHOW_STATISTICS ('AdventureWorks.Person.Contact',
'IX_Contact_EmailAddress') WITH DENSITY_VECTOR;

DBCC SHOW_STATISTICS ('AdventureWorks.Person.Contact',
'IX_Contact_EmailAddress') WITH HISTOGRAM;

These options are documented as part of the DBCC SHOW_STATISTICS command for SQL Server 2005. These options were actually available in SQL Server 2000, but they just aren’t documented. 

In SQL Server 2005, the first section contains a column of output called "String Index", which I like to call "String Statistics". I just wrote an article for about what these string statistics do for you.

Have fun!


Published Wednesday, February 13, 2008 3:18 PM by Kalen Delaney

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



Michael Zilberstein said:

STAT_HEADER and DENSITY_VECTOR can also be presented in a single row using the following syntax:

DBCC SHOW_STATISTICS ('AdventureWorks.Person.Contact',


February 13, 2008 5:50 PM

Kalen Delaney said:

Cool, thanks Michael!

February 13, 2008 5:54 PM

Michael Zilberstein said:

We know it from comments in system procedure's code. Here is what I've found while digging for some undocumented

March 24, 2008 3:31 AM

Marius said:

Hi all,

Is it possible to list all tables and all indexs in one out put file

July 6, 2009 6:31 AM

Doug said:

Is there some undocumented way to get the STAT_HEADER information (specifically the Rows & Rows Sampled values) without using DBCC SHOW_STATISTICS? I'd like to be able to create a function or view with the sample size for an index or stat, and I can't do that if I have to use an INSERT #temp EXEC('DBCC SHOW_STATISTICS(...) WITH STAT_HEADER')

October 27, 2011 7:57 PM

Kalen Delaney said:

Hi Doug

I have often wished for a DMV to show this information, so it can be accessed programmatically and tabularly.

There is a CONNECT item you can vote for:



October 28, 2011 9:49 PM

aaaa said:

mt0417 mt0417

April 16, 2018 9:01 PM

chenyingying said:


May 6, 2018 10:44 PM

linying123 said:


May 10, 2018 8:08 PM

shenyuhang said:


June 1, 2018 7:02 PM

dongdong8 said:



June 29, 2018 2:49 AM

linying123 said:



July 16, 2018 8:34 PM

dongdong8 said:



July 23, 2018 11:29 PM

shenyuhang said:


July 23, 2018 11:34 PM

cara meningkatkan nafsu makan said:

August 3, 2018 11:46 PM

chenjinyan said:


August 22, 2018 11:07 PM

obat bronkitis anak secara alami said:

August 24, 2018 7:17 PM

obat herbal kolesterol terbaik said:

August 24, 2018 11:06 PM

obat benjolan said:

September 4, 2018 12:40 AM

obat wasir said:

September 4, 2018 8:35 PM

obat katarak said:

September 17, 2018 1:57 AM

obat maag said:

September 18, 2018 7:38 PM

Leave a Comment


This Blog


Favorite Non-technical Sites or Blogs

Privacy Statement