THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | Join | Help
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 www.SQLCommunity.com about what these string statistics do for you.

http://www.sqlcommunity.com/default.aspx?tabid=77&id=178

Have fun!

~Kalen


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

Comments

 

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

'IX_Contact_EmailAddress') WITH STAT_HEADER JOIN DENSITY_VECTOR

February 13, 2008 5:50 PM
 

Kalen Delaney said:

Cool, thanks Michael!

February 13, 2008 5:54 PM
 

Jason Haley said:

February 14, 2008 10:06 AM
 

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

Leave a Comment

(required) 
(optional)
(required) 
Submit

This Blog

Syndication

Favorite Non-technical Sites or Blogs

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement