High sensitivity of SQL server 2016 to statistics ?

  • Jeff Moden

    SSC Guru

    Points: 996436

    To be clear, the link you posted from stack overflow cites TF 4199 and I'm not talking about that.  I'm talking about TF 9481, which fixed my issues when, like you, I migrated from 2012 to 2016.

     

    --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".

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

  • martinreiser

    SSC Enthusiast

    Points: 197

    In my opinion it is the same as "Legacy Cardinality Estimator" ON at database level.

    If yes, I mention this on the very first page.

    Martin

  • frederico_fonseca

    SSChampion

    Points: 14632

    martinreiser wrote:

    In my opinion it is the same as "Legacy Cardinality Estimator" ON at database level.

    If yes, I mention this on the very first page.

    Martin

    yes it is the same thing - but better in a way as it applies only to that db

  • martinreiser

    SSC Enthusiast

    Points: 197

    But is it is written on the very first page I do have it switched on - since the migration, I now about this switch. And I have still some problems, but fewer than without it.

    On Database level.

  • Jeff Moden

    SSC Guru

    Points: 996436

    Doing things at the database level isn't necessarily doing the same as doing something at the server level.  I strongly recommend that you try TF 9481 at the server level and see if that fixes things for you.  It did for me. YMMV.

    The other choice is to go fix all the queries that still have the problem.

    --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".

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

  • Grant Fritchey

    SSC Guru

    Points: 396475

    Sometimes the ultimate fix is a rewrite of the queries to a more optimal pattern.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • Jeff Moden

    SSC Guru

    Points: 996436

    Grant Fritchey wrote:

    Sometimes the ultimate fix is a rewrite of the queries to a more optimal pattern.

    Totally agreed... sometimes, it's the only fix.

    --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".

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

  • martinreiser

    SSC Enthusiast

    Points: 197

    Today I have a peformance issue where setting legacy cardinality estimator ON helps.

    From 100s to 1 sec. Really interesting.

     

  • Grant Fritchey

    SSC Guru

    Points: 396475

    martinreiser wrote:

    Today I have a peformance issue where setting legacy cardinality estimator ON helps.

    From 100s to 1 sec. Really interesting.

    If you have individual queries, and you can edit them, it is possible to add a hint to use the legacy estimator. I shy away from recommending this stuff because usually, the query needs editing anyway, so if we can modify the code, why not try to generally improve it rather than prop it up with a hint. However, it's a good thing to know.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • Jeff Moden

    SSC Guru

    Points: 996436

    martinreiser wrote:

    Today I have a peformance issue where setting legacy cardinality estimator ON helps.

    From 100s to 1 sec. Really interesting.

     

    Heh... that's not just "help"... that's a freakin' miracle and we found enough of those where we just said "screw it" and set the whole server back to using the legacy Cardinal Estimator.

    To be honest, I've not actually seen anywhere where the new Cardinality Estimator has actually made anything perform better.  As I've been known to say, "Change is inevitable... change for the better is not".

    --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".

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

Viewing 10 posts - 16 through 25 (of 25 total)

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