THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - 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 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
 

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:

https://connect.microsoft.com/SQLServer/feedback/details/611155/dbcc-show-statistics-info-should-be-available-as-a-dmv

Thanks

Kalen

October 28, 2011 9:49 PM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Favorite Non-technical Sites or Blogs

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