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

compare-plan.png (196.9 kB)
more ▼

asked Apr 24, 2017 at 01:58 PM in Default

avatar image

Håkan Winther
16.6k 37 46 58

Are you running same CE in both 2014 and 2016?

Apr 24, 2017 at 06:20 PM Kev Riley ♦♦

...and where did the image go?

Apr 24, 2017 at 06:37 PM Kev Riley ♦♦

Yes, we tried to change the cardinality estimator

Apr 24, 2017 at 07:46 PM Håkan Winther

I had to change the name of the image

Apr 24, 2017 at 07:49 PM Håkan Winther

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.

Apr 25, 2017 at 09:38 AM Kev Riley ♦♦
show all comments (comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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.

more ▼

answered Apr 28, 2017 at 08:23 AM

avatar image

Håkan Winther
16.6k 37 46 58

Interesting. I look forward to the blog post! :)

Apr 28, 2017 at 04:01 PM Kev Riley ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Apr 24, 2017 at 01:58 PM

Seen: 479 times

Last Updated: Apr 28, 2017 at 04:01 PM

Copyright 2018 Redgate Software. Privacy Policy