Estimated rows seems to be incorrect

• Robert Klimes

  SSCoach

  Points: 17865

  More actions

  May 19, 2016 at 9:31 am

  #327244

  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

  Bob
  -----------------------------------------------------------------------------
  How to post to get the best help[/url]