upgrade to 2016 causing performance problems

Question

upgrade to 2016 causing performance problems

MJ-1115929

Ten Centuries

Points: 1278
More actions
February 3, 2019 at 12:52 am

#382664

Hi all Experts,
We have strange problem (at least for our case), we have database in 2012 which was restored to 2016 as a part of upgrade , after restoration the compatibility mode stands on 2012 by default , first problem is the performance issues the queries take more time than usual while in 2016, upon searching it was identified because of the compatibility mode was in 2012 , so changed to 2016 (130) , it showed sudden change in performance 3.30 min query turned to show up in 2 seconds, and was happy to see, but here comes the real problem and 2nd most problem , some of the areas doesnt have issue but certain queries , especially reports was acting weird and taking ages to complete and some end up in timeouts.. so changed back the compatibility back to 2012, so currently we have queries running slow and have performance problems, so how this can be solved and tried force new query plans ? did recomplie option and sp_recompile but the results are the same 3.30 min or more, please help
Server config
OS - Windows server 2016
SQL - SQL 2016 enterprise with always on secondary
CPU 26 core
MEM- 136 GB
thanks
MJ

Viewing 15 posts - 1 through 15 (of 21 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

one of the know requirements when you upgrade is to update statistics for all tables, as the optimizer is different.
did you rebuild your indexes/update statistics yet, after upgrading to 2016?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

MJ-1115929 Ten Centuries Points: 1278 More actions · Answer 2

Lowell - Sunday, February 3, 2019 8:24 AM
one of the know requirements when you upgrade is to update statistics for all tables, as the optimizer is different.
did you rebuild your indexes/update statistics yet, after upgrading to 2016?

Thanks for the reply

Yes ofcourse , we have rebuild index ,reorganize automated daily job which runs based on the fragmentation level and sp_updatestats as first step before rebuild/reorg kicks in.

thanks
MJ

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 3

MJ-1115929 - Sunday, February 3, 2019 12:52 AM
Hi all Experts,
We have strange problem (at least for our case), we have database in 2012 which was restored to 2016 as a part of upgrade , after restoration the compatibility mode stands on 2012 by default , first problem is the performance issues the queries take more time than usual while in 2016, upon searching it was identified because of the compatibility mode was in 2012 , so changed to 2016 (130) , it showed sudden change in performance 3.30 min query turned to show up in 2 seconds, and was happy to see, but here comes the real problem and 2nd most problem , some of the areas doesnt have issue but certain queries , especially reports was acting weird and taking ages to complete and some end up in timeouts.. so changed back the compatibility back to 2012, so currently we have queries running slow and have performance problems, so how this can be solved and tried force new query plans ? did recomplie option and sp_recompile but the results are the same 3.30 min or more, please help
Server config
OS - Windows server 2016
SQL - SQL 2016 enterprise with always on secondary
CPU 26 core
MEM- 136 GB
thanks
MJ

Can you share more information such as execution plans, wait statistics, configuration details etc?
😎
Without any of those, anything is just a finger in the air!

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

There was a change in the Cardinality Estimator in (IIRC) 2014. When we made the change from 2012 to 2016, it did nothing for us but make several of our batch queries (your reporting queries would probably qualify as such a thing) a whole lot slower. We ended up using the Trace Flag mentioned in the following article to go back to using the old one.

https://docs.microsoft.com/en-us/sql/relational-databases/performance/cardinality-estimation-sql-server?view=sql-server-2017

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

frederico_fonseca SSCoach Points: 16157 More actions · Answer 5

Just to list the options.
to use the old cardinality estimator (CE 70) the following options are available - final result is the same insofar as it relates to CE

ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = ON; - available if on 2016 SP1 or higher - this is the best option as it keeps all other 2016 goodies
Trace Flag 9481 - less desired if set at server level - can be used at query level
database compatibility level lower than 120 - looses the remaining options available at higher db levels

if either of the above is set and the queries are still slow then other things are affecting the query - Max Dop, Cost Threshold, number of cores, memory (both max memory and memory availability vs old system), index/column stats. This assumes same volume of data.
I would also check traceflags set globaly on newer vs old server - never know if something was done (or not done) at this level

MJ-1115929 Ten Centuries Points: 1278 More actions · Answer 6

frederico_fonseca - Sunday, February 3, 2019 2:57 PM
Just to list the options.
to use the old cardinality estimator (CE 70) the following options are available - final result is the same insofar as it relates to CE
ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = ON; - available if on 2016 SP1 or higher - this is the best option as it keeps all other 2016 goodies
Trace Flag 9481 - less desired if set at server level - can be used at query level
database compatibility level lower than 120 - looses the remaining options available at higher db levels
if either of the above is set and the queries are still slow then other things are affecting the query - Max Dop, Cost Threshold, number of cores, memory (both max memory and memory availability vs old system), index/column stats. This assumes same volume of data.
I would also check traceflags set globaly on newer vs old server - never know if something was done (or not done) at this level

Dear frederico

thanks for the pointing workaround, we will try this config and post back the results here

once again thanks
regards
MJ

MJ-1115929 Ten Centuries Points: 1278 More actions · Answer 7

MJ-1115929 - Sunday, February 3, 2019 9:23 AM
Lowell - Sunday, February 3, 2019 8:24 AM
one of the know requirements when you upgrade is to update statistics for all tables, as the optimizer is different.
did you rebuild your indexes/update statistics yet, after upgrading to 2016?
Thanks for the reply
Yes ofcourse , we have rebuild index ,reorganize automated daily job which runs based on the fragmentation level and sp_updatestats as first step before rebuild/reorg kicks in.
thanks
MJ

@

MJ-1115929 - Sunday, February 3, 2019 9:23 AM
Lowell - Sunday, February 3, 2019 8:24 AM
one of the know requirements when you upgrade is to update statistics for all tables, as the optimizer is different.
did you rebuild your indexes/update statistics yet, after upgrading to 2016?
Thanks for the reply
Yes ofcourse , we have rebuild index ,reorganize automated daily job which runs based on the fragmentation level and sp_updatestats as first step before rebuild/reorg kicks in.
thanks
MJ

Dear Lowell
We are trying all the possibilities, if you have anything more to add any workarounds, please let us know.

thanks
Milesh

MJ-1115929 Ten Centuries Points: 1278 More actions · Answer 8

Jeff Moden - Sunday, February 3, 2019 1:55 PM
There was a change in the Cardinality Estimator in (IIRC) 2014. When we made the change from 2012 to 2016, it did nothing for us but make several of our batch queries (your reporting queries would probably qualify as such a thing) a whole lot slower. We ended up using the Trace Flag mentioned in the following article to go back to using the old one.
https://docs.microsoft.com/en-us/sql/relational-databases/performance/cardinality-estimation-sql-server?view=sql-server-2017

thanks for the reply Jeff,

We are going to try that, will post the outcome here..

thank once again

regards
MJ

MJ-1115929 Ten Centuries Points: 1278 More actions · Answer 9

Eirikur Eiriksson - Sunday, February 3, 2019 9:32 AM
MJ-1115929 - Sunday, February 3, 2019 12:52 AM
Hi all Experts,
We have strange problem (at least for our case), we have database in 2012 which was restored to 2016 as a part of upgrade , after restoration the compatibility mode stands on 2012 by default , first problem is the performance issues the queries take more time than usual while in 2016, upon searching it was identified because of the compatibility mode was in 2012 , so changed to 2016 (130) , it showed sudden change in performance 3.30 min query turned to show up in 2 seconds, and was happy to see, but here comes the real problem and 2nd most problem , some of the areas doesnt have issue but certain queries , especially reports was acting weird and taking ages to complete and some end up in timeouts.. so changed back the compatibility back to 2012, so currently we have queries running slow and have performance problems, so how this can be solved and tried force new query plans ? did recomplie option and sp_recompile but the results are the same 3.30 min or more, please help
Server config
OS - Windows server 2016
SQL - SQL 2016 enterprise with always on secondary
CPU 26 core
MEM- 136 GB
thanks
MJ
Can you share more information such as execution plans, wait statistics, configuration details etc?
😎
Without any of those, anything is just a finger in the air!

Dear Eirikur,

For Execution plan will the xml output help anyway ? , what additonal configuraiton details are required , server and sql details are in the first post.
thanks
MJ

MJ-1115929 Ten Centuries Points: 1278 More actions · Answer 10

MJ-1115929 - Monday, February 4, 2019 12:27 AM
frederico_fonseca - Sunday, February 3, 2019 2:57 PM
Just to list the options.
to use the old cardinality estimator (CE 70) the following options are available - final result is the same insofar as it relates to CE
ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = ON; - available if on 2016 SP1 or higher - this is the best option as it keeps all other 2016 goodies
Trace Flag 9481 - less desired if set at server level - can be used at query level
database compatibility level lower than 120 - looses the remaining options available at higher db levels
if either of the above is set and the queries are still slow then other things are affecting the query - Max Dop, Cost Threshold, number of cores, memory (both max memory and memory availability vs old system), index/column stats. This assumes same volume of data.
I would also check traceflags set globaly on newer vs old server - never know if something was done (or not done) at this level
Dear frederico
thanks for the pointing workaround, we will try this config and post back the results here
once again thanks
regards
MJ

Dear Jeff/Frederico,
We have tried the legacy setting with compatibility mode changed to 2016, but there was no much difference, result of the queries took the same amount of time. MAX DOP is set to 4 and cost of threshold is 5, what are the other areas to cover , please advise.

thanks
MJ

Jo Pattyn SSC-Dedicated Points: 32348 More actions · Answer 11

cost of threshold is 5 -> any reason for this low number?

MJ-1115929 Ten Centuries Points: 1278 More actions · Answer 12

Jo Pattyn - Tuesday, February 5, 2019 3:35 AM
cost of threshold is 5 -> any reason for this low number?

Dear Jo,
No specific reason apart from that it is default setting, we didnt change and checked, if wewant to what will be the recommended settings ?

awaiting your response.

thanks
MJ

Rick-153145 SSCrazy Points: 2823 More actions · Answer 13

By settings, we mean what does sp_configure return. Post that here along with what memory you have in the server itself.

Sorry, just looked at first post, cost threshold set to 5 and you have 26 cores? That won't help.

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 14

MJ-1115929 - Monday, February 4, 2019 12:39 AM
Eirikur Eiriksson - Sunday, February 3, 2019 9:32 AM
MJ-1115929 - Sunday, February 3, 2019 12:52 AM
Hi all Experts,
We have strange problem (at least for our case), we have database in 2012 which was restored to 2016 as a part of upgrade , after restoration the compatibility mode stands on 2012 by default , first problem is the performance issues the queries take more time than usual while in 2016, upon searching it was identified because of the compatibility mode was in 2012 , so changed to 2016 (130) , it showed sudden change in performance 3.30 min query turned to show up in 2 seconds, and was happy to see, but here comes the real problem and 2nd most problem , some of the areas doesnt have issue but certain queries , especially reports was acting weird and taking ages to complete and some end up in timeouts.. so changed back the compatibility back to 2012, so currently we have queries running slow and have performance problems, so how this can be solved and tried force new query plans ? did recomplie option and sp_recompile but the results are the same 3.30 min or more, please help
Server config
OS - Windows server 2016
SQL - SQL 2016 enterprise with always on secondary
CPU 26 core
MEM- 136 GB
thanks
MJ
Can you share more information such as execution plans, wait statistics, configuration details etc?
😎
Without any of those, anything is just a finger in the air!
Dear Eirikur,
For Execution plan will the xml output help anyway ? , what additonal configuraiton details are required , server and sql details are in the first post.
thanks
MJ

The plan XML should do fine, best would be to safe to .sqlplan file.
😎
The additional information is what is needed to build an identical instance, including hardware, OS and SQL configurations, I/O systems, file locations etc.