Same Execution Plan different stats

  • anvegger - Thursday, October 18, 2018 6:30 PM

    Jeff Moden - Thursday, October 18, 2018 5:53 PM

    Doing a DELETE isn't the way to fix this problem.  Fixing the code is.  And it's not an accidental CROSS APPLY... it's an accidental CROSS JOIN due to a lack of proper criteria.

    Sorry - misstyping : CROSS JOIN I meant ... The query is very simple. There is nothing to be even look at: a half  a dozen of tables simply  INNER JOIN to produce a zero result set over WHERE clause that filters an un-existing location.  I am lost and my brain stopped working

    So,.. if it's such a simple query, why did some nimrod decide to add an index hint to the query? 😉  Try removing that hint (which is actually a directive) and see what happens.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • I know you have said you updates stats, but did you do the update stats on both databases AFTER you restored them to this instance?

    What is the source version of SQL Server that these databases were restored from?

    Are they both in 2016 compatibility?

    Are the database settings for cardinality estimation the same?

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

  • Jack Corbett - Thursday, October 18, 2018 7:30 PM

    I know you have said you updates stats, but did you do the update stats on both databases AFTER you restored them to this instance?

    What is the source version of SQL Server that these databases were restored from?

    Are they both in 2016 compatibility?

    Are the database settings for cardinality estimation the same?

    I could certainly be wrong but I believe that the Index usage "hint" in the query is what's biting him.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • anvegger - Thursday, October 18, 2018 2:58 PM

    Schedule    175982         45016 KB    22904 KB    21928 KB    184 KB
    Schedule    179796         46136 KB    23384 KB    22344 KB    408 KB

    Many thanks for the wonderful picture DesNorton, but again - my actual number of records are increased in a small fraction of total numbers not in many times: 

    All tables are in a normal grow ratio - 10 % or less, it should not be 50 times slow... in stat that is my concern Or I am thinking in a wrong way totally. 

    Thanks for your help

    As Jeff stated, the issue could be with the index hint.
    It could also be with your stats.  Were they updated with the default sampling, or with a FULL SCAN.

  • DesNorton - Thursday, October 18, 2018 10:33 PM

    As Jeff stated, the issue could be with the index hint.It could also be with your stats.  Were they updated with the default sampling, or with a FULL SCAN.

    Many thanks DesNorton for your reference. That hint was introduced just to prove that both queries using 100% identical execution plan - object by object. That index was missing on Database D and I just added and forced using it. , Of cause all the tricks with INDEX and STATISTICS have been promoted several times, INDEX REBUILD, UPDATE STATISTICS WITH FULL SCAN, NORECOMPUTE - all these tricks were tested with no improvement. Something deeper than that is causing the slowness. 

    Many thanks foe looking into that

  • Jeff Moden - Thursday, October 18, 2018 6:54 PM

    Try removing that hint (which is actually a directive) and see what happens.

    Nothing is happening. Unfortunately.

  • I know you have said you updates stats, but did you do the update stats on both databases AFTER you restored them to this instance?
    AV: Yes - all UPDATE STATISTICS have been executed AFTER the RESTORE on the new instance

    What is the source version of SQL Server that these databases were restored from?

    AV: That is unknown - the environment have been dormant for at least 6 months

    Are they both in 2016 compatibility?
    AV: Yes (130 ) on both 

    Are the database settings for cardinality estimation the same?
    AV:  Yes - both databases have value of '0' :
    LEGACY_CARDINALITY_ESTIMATION    0

  • BTW that INDEX hint along with an INDEX itself did not change execution plan or stats - simply used a different index to produce the same amount of data consuming the same amount of resources
    LEFT JOIN SchedulePeriod sp WITH (INDEX (IX_N_SchedulePUResUsch))
    ON s.ScheduleId = sp.ScheduleId
    AND s.LicenseeId = sp.LicenseeId
    AND s.LocationId = sp.LocationId
    AND sp.ScheduleRepeatRuleId != 4

  • anvegger - Friday, October 19, 2018 7:16 AM

    BTW that INDEX hint along with an INDEX itself did not change execution plan or stats - simply used a different index to produce the same amount of data consuming the same amount of resources
    LEFT JOIN SchedulePeriod sp WITH (INDEX (IX_N_SchedulePUResUsch))
    ON s.ScheduleId = sp.ScheduleId
    AND s.LicenseeId = sp.LicenseeId
    AND s.LocationId = sp.LocationId
    AND sp.ScheduleRepeatRuleId != 4

    Then you need to investigate the data and the code and see why scalability has such a terrible effect.  Again, I believe it's because there's some missing criteria that simply causes and accidental cross join (many-to-many join) that's cause relational multiplication.  It may even be that the design of the data doesn't allow for further isolation and it's going to take you some time to science it out and fix it.  There's likely no magic bullet to be had here.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Yes you right Jeff - my next logical step would be to sync data in both cases (databases) and make sure that I eliminate or prove the impact of a data. DELETE will not help obviously but if I get the same execution stats from the same sets of my data - that will be a proof of the data is bad. Will keep digging into it further 

    Thanks for your help - Kindda makes sense to blame Hidden CROSS JOIN

  • anvegger - Friday, October 19, 2018 7:57 AM

    Yes you right Jeff - my next logical step would be to sync data in both cases (databases) and make sure that I eliminate or prove the impact of a data. DELETE will not help obviously but if I get the same execution stats from the same sets of my data - that will be a proof of the data is bad. Will keep digging into it further 

    Thanks for your help - Kindda makes sense to blame Hidden CROSS JOIN

    Before you do that try schema qualifying all tables in the query.

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • The cached plan size is different, why?
    Do you have Query Store on for both DBs?
    Are the system generated stats the same in both DBs?
    I find it interesting that the large table, indexes are seeks vs. scans.

  • Jeffrey Williams 3188 - Friday, October 19, 2018 8:09 AM

    Before you do that try schema qualifying all tables in the query.

    Good point - I do my best to check it out Thanks Jeff!

  • llefebvre7 - Friday, October 19, 2018 8:14 AM

    The cached plan size is different, why?
    Do you have Query Store on for both DBs?
    Are the system generated stats the same in both DBs?
    I find it interesting that the large table, indexes are seeks vs. scans.

    Excellent point: Yes Query Store is ON on both DBs: Statistics Collection Interval is different The rest is identical: 1h (Database A) vs 15 min (Database D)

  • anvegger - Friday, October 19, 2018 8:18 AM

    Good point - I do my best to check it out Thanks Jeff!

    Used a full object qualifiers on all tables: no impact

Viewing 15 posts - 31 through 45 (of 49 total)

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