one sec vs 40 minutes

  • I copied database from prod (sql 2008) to test (sql 2014) , changed compatibility mode to 120, re-index and updated stats
    when I run a stored procedure on old database it takes like 1 second, and same store procedure takes about 40 mins, it is obvious execution plan is different, Is there a way to capture & copy over the plan to new instance

  • goher2000 - Tuesday, November 27, 2018 10:03 AM

    I copied database from prod (sql 2008) to test (sql 2014) , changed compatibility mode to 120, re-index and updated stats
    when I run a stored procedure on old database it takes like 1 second, and same store procedure takes about 40 mins, it is obvious execution plan is different, Is there a way to capture & copy over the plan to new instance

    As you probably know, this is almost certainly down to the new cardinality estimator.
    Rather than trying to copy the plan, I suggest you either modify the queries, or set the appropriate trace flag against the particular queries which are causing issues.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • goher2000 - Tuesday, November 27, 2018 10:03 AM

    I copied database from prod (sql 2008) to test (sql 2014) , changed compatibility mode to 120, re-index and updated stats
    when I run a stored procedure on old database it takes like 1 second, and same store procedure takes about 40 mins, it is obvious execution plan is different, Is there a way to capture & copy over the plan to new instance

    The one reason I recommend people to avoid SQL Server 2014 is because of this issue. The new cardinality estimation engine sometimes causes problems. SQL Server 2014 offers no way to fix it other than turning off the new cardinality estimation engine, or modifying code to turn it off for a given query. I always suggest going to SQL Server 2016 or higher because the Query Store offers one more method of dealing with this issue. No, you still can't import a plan from another server, but you can let the cardinality estimation engine run under compatibility mode to capture the old plan, then force that plan. Almost the same thing.

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

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • goher2000 - Tuesday, November 27, 2018 10:03 AM

    I copied database from prod (sql 2008) to test (sql 2014) , changed compatibility mode to 120, re-index and updated stats
    when I run a stored procedure on old database it takes like 1 second, and same store procedure takes about 40 mins, it is obvious execution plan is different, Is there a way to capture & copy over the plan to new instance

    Can you confirm that the production environment matches the test environment in all respects other than version of SQL Server?  If so, then you're way luckier than most places.  🙂

    Thomas Rushton
    blog: https://thelonedba.wordpress.com

  • goher2000 - Tuesday, November 27, 2018 10:03 AM

    I copied database from prod (sql 2008) to test (sql 2014) , changed compatibility mode to 120, re-index and updated stats
    when I run a stored procedure on old database it takes like 1 second, and same store procedure takes about 40 mins, it is obvious execution plan is different, Is there a way to capture & copy over the plan to new instance

    It was a big problem for me when we upgraded.  As Grant Fritchey pointed out, they changed the cardinality estimator and just changing the compatibility mode won't fix it.  I don't have the number of the Trace Flag that says to use the old cardinality estimator but you can do a Yabingooglehoo for it and easily find it.

    To Phil Parkin 's point, though, employing the trace flag (once you find it) simply creates a form of technical debt that you'll eventually need to fix.  It would be best if you rewrote the stored procedure now, while you have the time (even if you don't think you have the time now).  And I don't recommend keeping a forced plan because things change and the plans should, too.

    --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)

  • Thank you all for your input, the stored procedure is working as expected with trace flag 9481,with 120 compatibility level, however we are going to rewrite the code to avoid any future problems.

  • goher2000 - Wednesday, November 28, 2018 7:34 AM

    Thank you all for your input, the stored procedure is working as expected with trace flag 9481,with 120 compatibility level, however we are going to rewrite the code to avoid any future problems.

    Sounds like the perfect plan... provided that they actually do follow through and let you rewrite the code.

    --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)

  • Grant Fritchey - Wednesday, November 28, 2018 4:28 AM

    goher2000 - Tuesday, November 27, 2018 10:03 AM

    I copied database from prod (sql 2008) to test (sql 2014) , changed compatibility mode to 120, re-index and updated stats
    when I run a stored procedure on old database it takes like 1 second, and same store procedure takes about 40 mins, it is obvious execution plan is different, Is there a way to capture & copy over the plan to new instance

    The one reason I recommend people to avoid SQL Server 2014 is because of this issue. The new cardinality estimation engine sometimes causes problems. SQL Server 2014 offers no way to fix it other than turning off the new cardinality estimation engine, or modifying code to turn it off for a given query. I always suggest going to SQL Server 2016 or higher because the Query Store offers one more method of dealing with this issue. No, you still can't import a plan from another server, but you can let the cardinality estimation engine run under compatibility mode to capture the old plan, then force that plan. Almost the same thing.

    In the larger interest of community just want to mention I was able to import the plan to new server, what I did was created guided plan using sp_create_plan_guide_from_handle on old box and scripted out plan guide and created it on new box, however it didn't worked.

  • even rewriting does not solve everything.

    CRM 2016 - DB on compat 130
    On my main CRM db in one of our tables (30 Milliion rows +) the following happen

    table with around 60 columns
    index on 2 columns - objecttype and object id
    straight update
    update table set field1 = xxx where objecttype = @vartype and objectid = @varid

    on this particular table, both objectype and objectid are null in ALL records.

    without changing the compatibility down, or setting up LEGACY_CARDINALITY_ESTIMATION the CE always chooses a full table scan to determine there is no row to update.

    Stats run fullscan on both table and index columns to no avail.

    so last resource was to set LEGACY_CARDINALITY_ESTIMATION on and it works as expected.
    (rewrite not possible as it is base code from Microsoft CRM dynamics)

  • frederico_fonseca - Wednesday, November 28, 2018 1:09 PM

    even rewriting does not solve everything.

    CRM 2016 - DB on compat 130
    On my main CRM db in one of our tables (30 Milliion rows +) the following happen

    table with around 60 columns
    index on 2 columns - objecttype and object id
    straight update
    update table set field1 = xxx where objecttype = @vartype and objectid = @varid

    on this particular table, both objectype and objectid are null in ALL records.

    without changing the compatibility down, or setting up LEGACY_CARDINALITY_ESTIMATION the CE always chooses a full table scan to determine there is no row to update.

    Stats run fullscan on both table and index columns to no avail.

    so last resource was to set LEGACY_CARDINALITY_ESTIMATION on and it works as expected.
    (rewrite not possible as it is base code from Microsoft CRM dynamics)

    Condolences.
    The best I can offer is: "At least it's not SharePoint"

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Viewing 10 posts - 1 through 9 (of 9 total)

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