Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Histogram question Expand / Collapse
Author
Message
Posted Friday, January 17, 2014 2:08 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 12:22 AM
Points: 43, Visits: 481

RANGE_HI_KEY RANGE_ROWS EQ_ROWS DISTINCT_RANGE_ROWS AVG_RANGE_ROWS
10005138 6055 137 330 18.34848
10007165 3262 212 95 34.33684




please 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

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 12:22 AM
Points: 43, Visits: 481
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

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 7:52 AM
Points: 156, Visits: 613
SELECT (10007165 - (10005138 + 1)) * 34.33684 = 69,566.43784


Since 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

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 12:22 AM
Points: 43, Visits: 481
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.43784

what does that figure means
and 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

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 7:52 AM
Points: 156, Visits: 613
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 rows

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?


___________________________
Do Not Optimize for Exceptions!
Post #1533508
Posted Wednesday, January 22, 2014 11:39 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 12:22 AM
Points: 43, Visits: 481
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

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 7:52 AM
Points: 156, Visits: 613
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
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse