Log in  ::  Register  ::  Not logged in

 Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Histogram question Rate Topic Display Mode Topic Options
Author
 Message
 Posted Friday, January 17, 2014 2:08 AM
 SSC Rookie Group: General Forum Members Last Login: 2 days ago @ 11:56 PM Points: 34, Visits: 319
 RANGE_HI_KEY RANGE_ROWS EQ_ROWS DISTINCT_RANGE_ROWS AVG_RANGE_ROWS10005138 6055 137 330 18.3484810007165 3262 212 95 34.33684please help from above histogram how QO it calculate "ESTIMATED ROWS" from below range col >10005139 and col <10005300. ===========================================performance issue:(1) Have you update the statistics on all tables of underling query?(2) Are you using index well on the tables?(3) Can you simplify your Query by re-writing it .(4) are indexes are Defragmented well ?
Post #1531960
 Posted Monday, January 20, 2014 2:16 AM
 SSC Rookie Group: General Forum Members Last Login: 2 days ago @ 11:56 PM Points: 34, Visits: 319
 HI,do you guys required some more information about above question please let me know .thanks ===========================================performance issue:(1) Have you update the statistics on all tables of underling query?(2) Are you using index well on the tables?(3) Can you simplify your Query by re-writing it .(4) are indexes are Defragmented well ?
Post #1532530
 Posted Tuesday, January 21, 2014 1:57 PM
 SSC-Enthusiastic Group: General Forum Members Last Login: Today @ 6:31 AM Points: 129, Visits: 574
 SELECT (10007165 - (10005138 + 1)) * 34.33684 = 69,566.43784Since all rows are between two values in histogram, the estimated number of rows is done by the following formula:(To - From - 1) * AVG_RANGE_ROWS for the first entry in the histogram greather than To value (in your case 10007165)The assumption is that values within the range are evenly distributed, so for each qualified col1 value about 34.3 rows are estimated. ___________________________Do Not Optimize for Exceptions!
Post #1533343
 Posted Tuesday, January 21, 2014 11:22 PM
 SSC Rookie Group: General Forum Members Last Login: 2 days ago @ 11:56 PM Points: 34, Visits: 319
 HI,thanks for the reply ,i used your formula but it`s working accurately.some question you used SELECT (10007165 - (10005138 + 1)) * 34.33684 = 69,566.43784what does that figure meansand it is not according to the formula.QO show 222.815 rows as estimation.is it documented how it works ===========================================performance issue:(1) Have you update the statistics on all tables of underling query?(2) Are you using index well on the tables?(3) Can you simplify your Query by re-writing it .(4) are indexes are Defragmented well ?
Post #1533472
 Posted Wednesday, January 22, 2014 2:15 AM
 SSC-Enthusiastic Group: General Forum Members Last Login: Today @ 6:31 AM Points: 129, Visits: 574
 Sorry, I totaly missed the number in the formula and in addition made a wrong assumption that all IDs in range are present in the table.Let's start again. Your histogram says that between 10005138 and 10007165 there are 95 distinct values. So, only 95 Ids in this range are present in the table (according to statistics). In your query you use a subset range from 10005139 to 10005300. Assuming even distribution in this range we expect 7.55 distinct values:SELECT 95*1.0*(10005300-10005139 )/(10007165-10005138)Per distinct value we expect 34.33684 (the column AVG_RANGE_ROWS for the entry 10007165), so the expected number of rows within a range is something like:SELECT 34.33684*95*1.0*(10005300-10005139 )/(10007165-10005138) = 259 rowsWhat I got is someting about 259 rows. You posted that QO came up with 222, did you use OPTION(RECOMPILE) at the end of the query to ensure that the plan did not come from cache? ___________________________Do Not Optimize for Exceptions!
Post #1533508
 Posted Wednesday, January 22, 2014 11:39 PM
 SSC Rookie Group: General Forum Members Last Login: 2 days ago @ 11:56 PM Points: 34, Visits: 319
 milos.radivojevic (1/22/2014)What I got is someting about 259 rows. You posted that QO came up with 222, did you use OPTION(RECOMPILE) at the end of the query to ensure that the plan did not come from cache?thanks for the replay again, option (recompile ) , YES i am aware of that behavior and use it. can you please tell me is this documented anywhere? ===========================================performance issue:(1) Have you update the statistics on all tables of underling query?(2) Are you using index well on the tables?(3) Can you simplify your Query by re-writing it .(4) are indexes are Defragmented well ?
Post #1533914
 Posted Thursday, January 30, 2014 3:34 AM
 SSC-Enthusiastic Group: General Forum Members Last Login: Today @ 6:31 AM Points: 129, Visits: 574
 Sorry for the late response. I don't know if it's somewhere officialy documented. I use to play with statistics and estimations and a lot of conclusions or behaviours I got by reverse engineering and testing. ___________________________Do Not Optimize for Exceptions!
Post #1536233

 Permissions