one sec vs 40 minutes

Question

one sec vs 40 minutes

goher2000

SSCertifiable

Points: 5479
More actions
November 27, 2018 at 10:03 am

#373167

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

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

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

Phil Parkin SSC Guru Points: 246970 More actions · Answer 1

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.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 2

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

Thomas Rushton SSC-Insane Points: 22649 More actions · Answer 3

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

Jeff Moden SSC Guru Points: 1004378 More actions · Answer 4

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)

goher2000 SSCertifiable Points: 5479 More actions · Answer 5

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.

Jeff Moden SSC Guru Points: 1004378 More actions · Answer 6

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)

goher2000 SSCertifiable Points: 5479 More actions · Answer 7

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.

frederico_fonseca SSCoach Points: 16161 More actions · Answer 8

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)

Phil Parkin SSC Guru Points: 246970 More actions · Answer 9

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"

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel