March 15, 2019 at 12:53 pm
I just restored a 2012 backup on a 2017 server and ran a rather complex statement, using different compatibility levels. Here are the times it took to execute the statement:
Compatibility 2017: 36s
Compatibility 2016: 36s
Compatibility 2014: 26s !!!
Compatibility 2012: 44s
Is there any sensed reason why there would be a significant improvement in performance when using 2014 compatibility level?
How can I achieve the same performance using 2017 compatibility level?
March 15, 2019 at 2:40 pm
cmartel 20772 - Friday, March 15, 2019 12:53 PMI just restored a 2012 backup on a 2017 server and ran a rather complex statement, using different compatibility levels. Here are the times it took to execute the statement:Compatibility 2017: 36s
Compatibility 2016: 36s
Compatibility 2014: 26s !!!
Compatibility 2012: 44sIs there any sensed reason why there would be a significant improvement in performance when using 2014 compatibility level?
How can I achieve the same performance using 2017 compatibility level?
It depends on what the query does and what the difference are in the execution plans. Did you compare the plans?
Sue
March 15, 2019 at 3:08 pm
As far as I can tell, the 2017 plan is identical to the 2014 plan (and isn't expected?)
March 15, 2019 at 9:42 pm
I would double check the plans are the same first. Not they are not expected to be the same. They vary by server config like trace flags and by version. I would then attempt to tune it or get MS on the line to explain the results.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply