SQL Server - Decoding Statistics Histogram (Statistics Internals)

Who is online?  113 guests and 0 members
home  »  blogs  »  Amit Bansal  »  SQL Server - Decoding Statistics Histogram (Statistics Internals)

SQL Server - Decoding Statistics Histogram (Statistics Internals)

change text size: A A A

posted  7/25/2012 4:27:09 AM  by Amit Bansal -  Views: [3149] 

Hi Friends,
Here is a short explanation of the statistics histogram. I am using AdventureWorks2008R2 database. There is a table Sales.SalesOrderHeader with a column TotalDue. Let us have a look at that column.
USE AdventureWorks2008R2
GO

-- first observe the rows
select TotalDue from Sales.SalesOrderHeader
GO

select DISTINCT TotalDue from Sales.SalesOrderHeader
GO
You will observe that out of some 31,000+ rows, there are some 4700+ distinct values – good data to demo statistics histogram.
I’ll go ahead and create a statistics object on this column. 
CREATE STATISTICS st_TotalDue ON Sales.SalesOrderHeader(TotalDue)
GO

Now, let’s observe the statistics histogram.
DBCC SHOW_STATISTICS(N'Sales.SalesOrderHeader', st_TotalDue)
GO

 The histogram output is as follows:
Let us understand this metadata.
As the name implies, Histogram divides those 31,000+ values in TotalDue column into various buckets. The RANGE_HI_KEY columns shows you the boundary values of those buckets. More precisely, each value there is the upper boundary value of a histogram step. The column RANGE_ROWS shows the number of rows where the TotalDue value is between two RANGE_HI_KEY values (mean between 2 boundaries). For example, there are 2 records between 2.5305 and 4.409; meaning there are 2 rows where the TotalDue column value is between 2.5305 and 4.409 (excluding the boundaries). Let us verify this.
select COUNT(*) from sales.SalesOrderHeader
where TotalDue>2.5305 and TotalDue<4.409

The EQ_ROWS shows the number of rows where TotalDue column value is equal to the RANGE_HI_KEY value. For example, there are 139 rows equal to 2.5305; meaning there are 139 rows where the TotalDue column value is equal to 2.5305 which is a boundary value. Let us verify:
select COUNT(*) from sales.SalesOrderHeader
where TotalDue=2.5305

The DISTINCT_RANGE_ROWS shows the number of distinct records between two RANGE_HI_KEY values; meaning the number of records where the TotalDue column value is unique between RANGE_HI_KEY values. For example, there is one distinct value between 2.5305 and 4.409. Let us verify:
select DISTINCT TotalDue from sales.SalesOrderHeader
where TotalDue>2.5305 and TotalDue<4.409

You can see that there is only one row output. Recollect that there are only 2 rows that falls between this range – remember RANGE_ROWS?
Which means, these 2 records should be identical considering TotalDue column value? Let us verify:
select TotalDue from sales.SalesOrderHeader
where TotalDue>2.5305 and TotalDue<4.409

Last but not the least, AVG_RANGE_ROWS shows the average number of rows per distinct value; meaning if you have 10 rows in a particular range and there are 5 distinct value out of them, AVG_RANGE_ROWS will show you 2 (10 divided by 5). In our case here its 2 divided by 1 = 2.
So how the query optimizer estimates the number of rows it has to process? And what is cardinality estimation? You have some answers now :)
If you liked this post, do like us on FaceBook at http://www.FaceBook.com/SQLServerGeeks
Have a SQL Server question? Join the fastest growing SQL Server facebook group at: http://www.facebook.com/groups/458103987564477/
Regards,
Amit Bansal
Visit my FaceBook page at http://www.facebook.com/AmitRSBansal
Contribute on SQLServerGeeks.com: visit http://www.sqlservergeeks.com/default-category/write-for-us
tags : SQL Server Statistics internals, EQ_ROWS, RANGE_HI_KEY, RANGE_ROWS, SQL Server statistics histogram, understanding SQL Server statistics
  Rate This Blog Entry:  register  or  login

Author

AmitBansal Amit Bansal (Member since: 3/12/2011 4:59:54 PM)
Follow Amit at Twitter @A_Bansal : Amit Bansal is the CTO of eDominer Systems & Peopleware India. He is a consultant, trainer, writer, speaker & evangelist on SQL Server & Business Intelligence. A seasoned speaker; he speaks at major summits and conferences of Microsoft worldwide including TechED, MCT Summits, MSDN & TechNet conferences. Over the last 8 years, he has consulted, trained & mentored more than 6000 IT professionals on SQL Server & Business Intelligence and worked with top notch blue chip companies worldwide including Microsoft, Infosys, Wipro, RBS, HCL, HP, Siemens, IBM, Accenture, etc. He has delivered more than 400 workshops on SQL Server & Business Intelligence. Apart from holding many Microsoft credentials, he is also a Microsoft Most Valuable Professional (MVP) awardee on SQL Server. He is also one of the first Microsoft Certified Trainer in India. Amit has worked with Microsoft in India and US as a Subject Matter Expert in various capacities participating in OD sessions, technical reviewing, etc. He is also a very active community lead in Asia Pacific. Visit www.amitbansal.net to know more. Subscribe to Amit’s blog and be a member of his technical forum at www.SQLServerGeeks.com - an exclusive SQL Server portal. Visit www.peoplewareindia.com for Corporate Training solutions. FaceBook – http://www.facebook.com/people/Amit-Bansal/525339346 LinkedIn – http://www.linkedin.com/pub/amit-bansal/7/121/755

View Amit Bansal 's profile

Comments (no comments yet)

Leave a comment

Name:
  *  
Email:  (your email is kept secure)
 *  
Website:

example: "http://www.SQLServerGeeks.com"

 
Type the characters:
 *
 
   

Training on Microsoft Products & Technologies

Email Subscriptions

   Get the Most Recent Blogs in your inbox
Blogs RSS Feed

Amit Bansal's latest blog posts

Blogs RSS Feed

Latest community blog posts

  • Parse DBCC MemoryStatus using Powershell
    5/19/2013 10:58:46 AM by Ahmad Osama  - Views: 166  - Comments: 0
    A few days back I was resolving a memory issue which caused sql server to stop responding. I was able to figure out the issue by looking into DBCC MemoryStatus output. It was the full text search whic...
  • DB-Migrate v2: Powershell Module to Migrate Databases
    5/14/2013 8:11:24 PM by Ahmad Osama  - Views: 241  - Comments: 0
    This is an update to the DB-Migrate ( http://www.sqlservergeeks.com/blogs/AhmadOsama/personal/797/db-migrate-a-powershell-module-to-migrate-databases ) powershell module to migrate database between sq...
  • The TSQL Classes Stored Procedures: Transaction Handling
    5/12/2013 4:14:12 PM by Ahmad Osama  - Views: 266  - Comments: 0
    This blog is part of the series The TSQL Classes: Stored Procedure The links to previous post are given below. http://sqlservergeeks.com/blogs/AhmadOsama/personal/762/the-tsql-classes-stored-procedure...
  • Reporting With SSRS Part 1 -With Sample Report.
    5/9/2013 4:33:03 AM by Syed Asrar Ali  - Views: 156  - Comments: 0
    Reporting With SSRS Part 1 : In this serires of blogs, i will try to introduce you with some of the key features of the tool which will help you get on board to become a proficient report developer.If...
  • SQL Server – What is IDENTITY_INSERT?
    5/8/2013 2:31:43 PM by Piyush Bajaj  - Views: 1049  - Comments: 3
    Hi Folks, I am sure that all of you would be aware about the role of Identity column in a table, i.e., it is column which keeps on incrementing without supplying the value explicitly during insertion....
  • DB-Migrate: A PowerShell Module to Migrate Databases.
    5/5/2013 8:48:46 PM by Ahmad Osama  - Views: 435  - Comments: 0
    Recently I wrote powershell scripts to move databases, logins and SQL Agent Jobs between instances. Another one I wrote was to fix orphan users. I have now combined all of these different functions in...
  • SQL Server blogs from Ahmad Osama for the month of April
    5/3/2013 10:22:05 AM by Admin Admin  - Views: 345  - Comments: 0
    Hi SQL Geeks, Here are the blog posts by Ahmad Osama for the month of April. We would expect more of his informative blogs this month. Happy Learning! If you like our blogs do like us on Facebook at h...
  • The TSQL Classes Stored Procedures: Error Handling
    5/1/2013 9:05:11 PM by Ahmad Osama  - Views: 750  - Comments: 0
    This blog is part of the series The TSQL Classes: Stored Procedure The link to previous post are given below. http://sqlservergeeks.com/blogs/AhmadOsama/personal/762/the-tsql-classes-stored-procedures...
  • Get free space in all files in an Instance
    4/8/2013 9:01:44 AM by Manohar Punna  - Views: 1660  - Comments: 1
    As DBAs we are always challenged of unexpected size growth of log or data files and need to shrink files based on available free space in each file. Most of the times we are stuck and do a lot of scri...
  • SQL Server: Using Powershell to Fix Orphan Users
    4/2/2013 7:05:50 PM by Ahmad Osama  - Views: 1700  - Comments: 0
    One of the major migration activities is to fix orphan users. Though, it can be easily fixed by sp_change_users_login procedure, however what if you need to do this for multiple servers. A powershell ...
  • The TSQL Classes Stored Procedures: Parameter sniffing
    4/1/2013 10:55:43 PM by Ahmad Osama  - Views: 2432  - Comments: 3
    This blog is part of the series The TSQL Classes: Stored Procedure. http://www.sqlservergeeks.com/blogs/AhmadOsama/personal/764/the-tsql-classes-stored-procedures-nested-proc-recursion-and-plan-cachin...