none
Query is slower in SQL 2016, whereas running faster in 2012.

    Question

  • Hi all,

    we are testing to upgrade SQL 2016 from SQL 2012. One of the simple query running 1 sec in SQL 2012 but running 10 sec in SQL 2016 (Tested in both version of SQL). So I found there is a change in query plan, and 2012 pick up most faster query but SQL 2016 not.

    I was expecting not so much performance difference. But, this is poor. One thing I found when comparing both plan, SQL 2012 is running parallel but 2016 not. Can anyone tell me what is the problem and how to find out the issue ? to use faster plan. (FYI, I don't wanna use Query Store introduced in SQL 2016)

    Below are piece of both plan, which I really suspect

    Faster Plan (SQL 2012)

    2012

    Slower Plan (SQL 2016)

    2016


    Maharajan

    Monday, April 10, 2017 6:40 AM

Answers

  • Olaf,

    I changed the CE, it runs faster. So it means I need rewrite queries, But the query which I am using is fewer JOIN, I really don't have any idea how to rewrite those JOIN..

    You are facing regression and that is for sure. Either update stats and rebuild indexes and try with 130 compatibility level or you have to use 110 comp level. If you still want to use 130 comp level you might as well need to rewrite the query or may be ask developer to do it.

    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Monday, April 10, 2017 9:23 AM
    Moderator

All replies

  • Hello Maharajan,

    It's possible that you hit the new Cardinality Estimation (SQL Server) introduced in SQL Server 2014, see SQL Server 2014’s new cardinality estimator (Part 1)

    A quick test is to change database comp level back to SQL Server 2012 and re-run the query. If it's the CE then you have to rewrite your queries.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Monday, April 10, 2017 7:46 AM
  • Olaf,

    I changed the CE, it runs faster. So it means I need rewrite queries, But the query which I am using is fewer JOIN, I really don't have any idea how to rewrite those JOIN..


    Maharajan

    Monday, April 10, 2017 8:21 AM
  • Olaf,

    I changed the CE, it runs faster. So it means I need rewrite queries, But the query which I am using is fewer JOIN, I really don't have any idea how to rewrite those JOIN..

    You are facing regression and that is for sure. Either update stats and rebuild indexes and try with 130 compatibility level or you have to use 110 comp level. If you still want to use 130 comp level you might as well need to rewrite the query or may be ask developer to do it.

    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Monday, April 10, 2017 9:23 AM
    Moderator
  • Hi Maharajan,

    You might not need to re-write the query at this moment as there are options left for you to enforce old CE:
    1. To enforce legacy CE at database level, you could go with ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = ON.
    2. To enforce legacy CE at query level, use USE HINT(‘FORCE_LEGACY_CARDINALITY_ESTIMATION’) in your query.(not recommended)
    3. And if you have utilized Query Store in your database, you could also force use the preferred execution plan without modify your existing query. For more information, please review this blog.

    If you have any other questions, please let me know.

    Regards,
    Lin

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, April 10, 2017 9:24 AM
    Moderator
  • Hello Lin,

    In my understanding, 

    1. SCOPED_CONFIGURATION is still database level, So I think I can not avail full benefit of new CE for other queries.

    2. Enforce HINT, most of my queries are database view. So I don't think I can use HINT(‘FORCE_LEGACY_CARDINALITY_ESTIMATION’).

    3. I checked query store, I can force the preferred plan, but one thing I am afraid is, right now I am testing in test server. So If I move to production, I have to do that force again. until I force to use preferred plan, our user might feel poor performance and it is not really a good thing for running system. :(

    Do you my understandings are correct , and is there any other option ?


    Maharajan

    Monday, April 10, 2017 9:35 AM
  • Shanky, 

    It solved based on your advice.

    I updated statistics, and rebuild indexes, now it runs as I expected.

    Thanks for great help.


    Maharajan

    Monday, April 10, 2017 10:26 AM