May 19, 2016 at 9:31 am
I have come across an issue where the estimated rows dont appear to be what they should be. I may misunderstand how estimated are derived so I am just looking for some clarification. I will apologize in advanced for not being able to supply a working demo of the issue as I have not been able to re-create is in any other database but same issue occurs in any table I have tried within database.
The server is 2014 RTM-CU10 (cant update to SP1 because application not certified yet) and the server collation is Latin1_General_BIN.
here is an example of table
CREATE TABLE [dbo].[PERSON](
[ID] [varchar](10) NOT NULL,
[FIRST_NAME] [varchar](30) NULL,
CONSTRAINT [PK_PERSON] PRIMARY KEY CLUSTERED
([ID] ASC))
CREATE NONCLUSTERED INDEX [IX_PERSON_FIRST_NAME] ON [dbo].[PERSON]([FIRST_NAME] ASC)WITH (FILLFACTOR = 80)
below is an excerpt from the histogram when running DBCC Show_statistics('PERSON','IX_PERSON_FIRST_NAME')
RANGE_HI_KEYRANGE_ROWSEQ_ROWSDISTINCT_RANGE_ROWSAVG_RANGE_ROWS
Laurie5352191403.821429
Lee9801532334.206008
Leslie13932503603.869444
Linda9568253163.025316
now when I run the following query I get 153 estimated rows.
SELECT ID,FIRST_NAME
FROM PERSON
WHERE FIRST_NAME = 'Lee David'
OPTION(RECOMPILE)
<RelOp AvgRowSize="24" EstimateCPU="0.0003253" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="153" LogicalOp="Index Seek" NodeId="0" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="0.0034503" TableCardinality="268380">
Books online states the RANGE_HI_KEY is "Upper bound column value for a histogram step" so shouldn't the predicate "Lee David" fall into the next step(RANGE_HI_KEY = Leslie) and have an estimate of 3.869444.
Am I misunderstanding how this should work or is this not working the way it should? Thanks
Viewing 0 posts
You must be logged in to reply to this topic. Login to reply