Post reply

Index Seek with a secondary Predicate - How does SQL Server estimates the number of rows

  • November 30, 2015 at 5:03 am

    #306677

    I have an Index Seek operator that shows also a Predicate (which is not the seek one) being executed on the included columns of the index. I know that SQL Server looks the histogram to estimate the number of rows that will come out of the operator, my doubt is: that second predicate, does it influences SQL Server estimated number of rows? If so, how does it happen (once the second predicate is over a column that is not on the histogram of the index, because it is a included column).

  • November 30, 2015 at 5:08 am

    #1842836

    Yes it will. Not 100% sure, but I think it'll check the statistics for the column involved in the predicate (as in, the column stats for it) and it multiplies the % together. So if one predicate alone returns 10% of the table and the other alone returns 50% of the table, the estimate for the two combined will be 5% of the table. That's for 2012 and before. The 2014 cardinality estimator behaves differently.

    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

  • November 30, 2015 at 5:47 am

    #1842846

    That clarifies a lot. Thanks for the help.

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

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