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