Post reply

Cardinality Estimation for >= / > for intra step statistics value

  • September 1, 2016 at 5:34 am

    #312952

    I am trying to understand how SQL Server try to estimate for greater than and greater than equal where clauses in SQL Server 2014.

    I think I do understand the cardinality estimation when it hits the step for example, if i do

    select * from charge where charge_dt > '1999-10-13 10:47:38.550'

    The cardinality estimation is 6672 which can be easily calculated as 32(EQ_ROWS) + 6624(RANGE_ROWS) + 16 (EQ_ROWS) = 6672 (histogram in below screenshot)

    But as when i do select * from charge where charge_dt >= '1999-10-13 10:48:38.550' the estimate is 4844.13.

    How is that calculated?

  • September 1, 2016 at 5:41 am

    #1898729

    A portion of the RANGE_ROWS, based on how far in to the interval SQL expects the specified predicate value to be, plus 16, the EQ_ROWS for the high key.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply