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.
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.
answered Apr 28, 2017 at 08:23 AM