Lies damned lies and statistics - Part IIhttp://sqlblog.com/blogs/elisabeth_redei/archive/2009/08/10/lies-damned-lies-and-statistics-part-ii.aspxLies damned lies and statistics - Part II In previous post I listed situations where your statistics might need some special attention ( http://sqlblog.com/blogs/elisabeth_redei/archive/2009/03/01/lies-damned-lies-and-statistics-part-i.aspx ). As I mentionedenCommunityServer 2.1 SP2 (Build: 61129.1)re: Lies damned lies and statistics - Part IIhttp://sqlblog.com/blogs/elisabeth_redei/archive/2009/08/10/lies-damned-lies-and-statistics-part-ii.aspx#15945Tue, 11 Aug 2009 03:28:08 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:15945jchang<p>in this specific example, when you are looking for a row that does not exist, it does not matter what the statistics sampling is, a good estimate is frequently not possible.</p>
<p>The presumption is that one is looking for a row that does exist.</p>
<p>Lets assume that value is not a Range_Hi_Key value but within the bounds of the low and high Range_Hi_Key,</p>
<p>then the only info available is from the Distinct_Range_Rows and Avg_Range_Rows.</p>
<p>Suppose we are looking for value = 550 and the lower Range_Hi_Key is 500 and the next Range_Hi_Key is 600, and the Distinct_Range_Rows is 30, and the Avg_Range_Rows is 100.</p>
<p>Of the 99 possible values between 500 and 600, only 30 exist, for which the average distribution is 100.</p>
<p>The query plan for value = 550 does not consider that there is a 2/3 chance of no values, the presumption is that we are searching for one of the 30 that do exist.</p>
<p>So I think the only time a zero return will show (as estimate 1 row) is if the value is outside the bounds, or if the Range_Rows is zero, which sometimes means there are fewer than 200 distinct values.</p>