7

I have a table with 5,000,000 records with the DateOfBirth column spread between 1950 and 2007. The statistics histogram for the index only has two RANGE_HI_KEYs. I feel like the histogram should have more buckets given the quantity of records and selectiveness of the field.

Can anyone explain to me why SQL Server is only using two RANGE_HI_KEYs?

NOTE: I get the same stats breakdown on 2014 and 2016

Table

CREATE TABLE [dbo].[Person](
    [BusinessEntityID] [INT] NOT NULL,
    [PersonType] [NCHAR](2) NOT NULL,
    [NameStyle] [dbo].[NameStyle] NOT NULL,
    [Title] [NVARCHAR](8) NULL,
    [FirstName] [dbo].[Name] NOT NULL,
    [MiddleName] [dbo].[Name] NULL,
    [LastName] [dbo].[Name] NOT NULL,
    [Suffix] [NVARCHAR](10) NULL,
    [EmailPromotion] [INT] NOT NULL,
    [rowguid] [UNIQUEIDENTIFIER] NOT NULL,
    [ModifiedDate] [DATETIME] NOT NULL,
    [DateOfBirth] [DATE] NOT NULL
)

Index

CREATE NONCLUSTERED INDEX [IX_dbo_Person_DateOfBirth] 
ON [dbo].[Person] ( [DateOfBirth] ASC )

Histogram

Statistics for INDEX 'IX_dbo_Person_DateOfBirth'.
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Name                            Updated                         Rows                            Rows Sampled                    Steps                           Density                         Average Key Length              String Index                    
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
IX_dbo_Person_DateOfBirth       Jun 24 2016  7:16PM             5000000                         5000000                         2                               0.004236792                     3                               NO                                                              5000000                         

All Density                     Average Length                  Columns                         
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
4.720544E-05                    3                               DateOfBirth                     

Histogram Steps                 
RANGE_HI_KEY                    RANGE_ROWS                      EQ_ROWS                         DISTINCT_RANGE_ROWS             AVG_RANGE_ROWS                  
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1/1/1950 12:00:00 AM            0                               255                             0                               1                               
12/31/2007 12:00:00 AM          4999537                         208                             21182                           236.0276                        
3

Very easy. Look for the result of:

select datediff(day,'1/1/1950','12/31/2007');

It will give you your DISTINCT_RANGE_ROWS + 1
That means that SQL Server thinks that all your DOBs are approximately equally distributed across the range with average 236 items per day.
Query your data and see if you have any outlines like: missing day ranges or >1000 DOBs per day.
If you do, than that would be a question to the engine.

  • Thanks Slava. So the hi keys are based on data skew rather than quantity or spread! – SQL Hammer Jun 25 '16 at 0:59
  • @SQLHammer RANGE_HI_KEY is the actual value in the table. RANGE_ROWS are how many rows fall between the last RANGE_HI_KEY and the current RANGE_HI_KEY. – swasheck Jun 25 '16 at 1:02
  • You are right. If you are interested, I have one very good interactive slide on that in sqlpass.org/EventDownload.aspx?suid=11095 – Slava Murygin Jun 25 '16 at 1:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.