question

Håkan Winther avatar image
Håkan Winther asked

Query slower in SQL2016 than SQL2014

We have a query that is constantly 500 ms slower in SQL 2016. The databases are exactly the same, the statistics are updated, the hardware and configurations are the same, but the query is slower. After comparing the execution plans we can see that in SQL 2014 we get a sort operator that reduces the number of records before a nested loop and then a top operator after the nested loop, but in SQL 2016 there is a sort top n operator after the nested loop. As the number of executions in the nested loop for SQL 2016 is higher it takes longer time. Why do SQL 2016 choose the order top n after the nested loop instead of before. Or why are the sort operator reducing data even though it's not a distinct sort? Yes, we know it's possible to get rid of the key lookups by changing the index, but the client doesn't want to do any changes at this stage and wants to know why SQL 2016 is slower. We have tried some trace flags and compatibility modes to see if we can get the same plan without any success. ![alt text][1] [1]: /storage/temp/4060-compare-plan.png
performancesql server 2014sql-server-2016execution plansort
compare-plan.png (192.3 KiB)
11 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image Kev Riley ♦♦ commented ·
IF CE is same, what build numbers from each instance? Maybe an update in a service pack or something could lead you to understand the changes.
2 Likes 2 ·
Kev Riley avatar image Kev Riley ♦♦ commented ·
Are you running same CE in both 2014 and 2016?
0 Likes 0 ·
Kev Riley avatar image Kev Riley ♦♦ commented ·
...and where did the image go?
0 Likes 0 ·
Håkan Winther avatar image Håkan Winther commented ·
Yes, we tried to change the cardinality estimator
0 Likes 0 ·
Håkan Winther avatar image Håkan Winther commented ·
I had to change the name of the image
0 Likes 0 ·
Show more comments

1 Answer

·
Håkan Winther avatar image
Håkan Winther answered
By changing the compatibility level to 120 and change the database scoped setting "Query Optimizer Fixes" to OFF, the old SQL 2014 plan is used and the performance are back to normal, BUT then all the new stuff from SQL 2016 and all other performance improvements are lost. We have to do more tests to see if there are other performance issues, otherwise we can convince the customer to fix the query by adding the "missing column" to the index used and avoiding the last nested loop (that will improve the performance in SQL 2014 also). Thanks for your effort to help us solve the mystery with the sort top n (that normally would kick in if the number of records are less then 100). In this case it's a implicit correlated subquery with select top 1, that will execute 1218 times and the optimizer doesn't consider how many times the correlated subquery is executed.
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image Kev Riley ♦♦ commented ·
Interesting. I look forward to the blog post! :)
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.