June 8, 2018 at 4:15 am
Hello,
We are planning to move from SQL 2012 to 2017 for the "Perfomance Improvement", on our initial load test there was a degradation of ~20%. Later, changed the db compatibility 2017 and ran the load test again eager to see the magic, but horrified to see the cpu at 100% tried changing Max DOP to 4/8 and cost threshold to 30(on our 24 core VM) to find the sweet spot, but had no luck with it . Am I missing something here? can someone point me to the right direction?
Sorry for my bad English..
June 8, 2018 at 7:00 am
Hi,
have you "cleaned" your plancache, and your statistics?
Have you checked your waits during / after the test?
What about the execution plans from your queries?
Have you got some kind of monitoring tool, to take a look on your sql server ?
Best regards,
Andreas
June 8, 2018 at 8:31 am
dropsa999 - Friday, June 8, 2018 4:15 AMHello,
We are planning to move from SQL 2012 to 2017 for the "Perfomance Improvement", on our initial load test there was a degradation of ~20%. Later, changed the db compatibility 2017 and ran the load test again eager to see the magic, but horrified to see the cpu at 100% tried changing Max DOP to 4/8 and cost threshold to 30(on our 24 core VM) to find the sweet spot, but had no luck with it . Am I missing something here? can someone point me to the right direction?
I could actually be the "improvement" that that made to the Cardinality Estimator prior to 2017. We had a similar problem when we migrated from 2012 to 2016 with a good amount of our core code. Thankfully, there's a Trace Flag that allows you to go back to using the old Cardinality Estimator in 2016 and, hopefully, they've carried that forward to 2017. I don't remember the Trace Flag number and I don't have a link for it but you should be able to scare up that bit of information in Google pretty quickly.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 8, 2018 at 8:32 am
dropsa999 - Friday, June 8, 2018 4:15 AMHello,
We are planning to move from SQL 2012 to 2017 for the "Perfomance Improvement", on our initial load test there was a degradation of ~20%. Later, changed the db compatibility 2017 and ran the load test again eager to see the magic, but horrified to see the cpu at 100% tried changing Max DOP to 4/8 and cost threshold to 30(on our 24 core VM) to find the sweet spot, but had no luck with it . Am I missing something here? can someone point me to the right direction?
Since you could be dealing with issues related to the Cardinality Estimator, have you tried addressing that issue? This article has some of the suggestions, options:
SQL Server 2016 new features to deal with the new CE
Sue
June 8, 2018 at 9:37 pm
Sue_H - Friday, June 8, 2018 8:32 AMdropsa999 - Friday, June 8, 2018 4:15 AMHello,
We are planning to move from SQL 2012 to 2017 for the "Perfomance Improvement", on our initial load test there was a degradation of ~20%. Later, changed the db compatibility 2017 and ran the load test again eager to see the magic, but horrified to see the cpu at 100% tried changing Max DOP to 4/8 and cost threshold to 30(on our 24 core VM) to find the sweet spot, but had no luck with it . Am I missing something here? can someone point me to the right direction?Since you could be dealing with issues related to the Cardinality Estimator, have you tried addressing that issue? This article has some of the suggestions, options:
SQL Server 2016 new features to deal with the new CESue
Thanks for your reply, the issue is wit the CE ofcourse the same query which is running in <5 secs with the Compatability level 11 is taking more than 9 minutes, will try enabling the trace flag or switching the option 'FORCE_LEGACY_CARDINALITY_ESTIMATION'.
Sorry for my bad English..
June 8, 2018 at 10:31 pm
andreas.kreuzberg - Friday, June 8, 2018 7:00 AMHi,
have you "cleaned" your plancache, and your statistics?
Have you checked your waits during / after the test?
What about the execution plans from your queries?Have you got some kind of monitoring tool, to take a look on your sql server ?
Best regards,
Andreas
Thanks fro your reply, I have cleaned the cache, The wait type associated with the query is SOS_SCHEDULER_YIELD, both the execution plans are similar.
Sorry for my bad English..
June 9, 2018 at 3:48 am
This is very interesting, can you post both execution plans please?
😎
Question, are the sql server configurations the same for both?
I've seen huge performance improvements going from 2012 or earlier up to 2014 and later, not tested in any detail the difference between 2014,2016 and 2017 though.
June 9, 2018 at 5:41 am
From multiple previous experiences upgrading, it'll be a small number of queries that have degraded in performance. Identify them, and either rewrite or use a hint to get them to behave.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 9, 2018 at 9:10 am
Eirikur Eiriksson - Saturday, June 9, 2018 3:48 AMThis is very interesting, can you post both execution plans please?
😎
Question, are the sql server configurations the same for both?I've seen huge performance improvements going from 2012 or earlier up to 2014 and later, not tested in any detail the difference between 2014,2016 and 2017 though.
I have been testing the query on the same db by changing the compatability levels.
Sorry for my bad English..
June 9, 2018 at 9:15 am
Could you post the queries and execution plans, the ones using the most CPU only.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply