Different Execution plan and performance issues in SQL 2017 compare to SQL 2012

  • nirav.gandhi7

    SSC-Addicted

    Points: 415

    We have recently migrated from SQL Server 2012 Express to SQL Server 2017 Express and observed some of queries having performance issues in SQL Server 2017.

    We have compared execution plan and found that both versions generate different execution plan. As Microsoft claims SQL Server 2017 is faster than earlier version of SQL server but we get reverse result.

    We have used “LEGACY_CARDINALITY_ESTIMATION” hints and applied temporary solution.

    We have changed DB Compatibility Level from 110 for 2012 to 140 for 2017.

    UPDATE STATISTICS & Rebuild index

    Does anyone have any ideas ?

    How can we apply permanent solution?

    How can we proof SQL Server 2017 is faster compare to SQL Server 2012?

    How can find impact of queries which are not working SQL Server 2017?

    • This topic was modified 2 months, 3 weeks ago by  nirav.gandhi7.
    • This topic was modified 2 months, 3 weeks ago by  nirav.gandhi7.
  • Grant Fritchey

    SSC Guru

    Points: 395580

    In the vast majority of cases, 2017 is radically faster than 2012. However, in some edge cases, as you may be experiencing, the opposite is true. Usually, this involves situations where the queries in question are badly written, the structures are not properly configured, the statistics are skewed, or all of the above. There was a lot more give to the old query engine for badly written queries. The newer versions are a little less forgiving in some areas.

    The way I would approach this is to enable Query Store and set the database compatibility level to the old version. Then run the load for a little while that way (a few days or weeks, you'll have to decide). Then, switch the compatibility level. Some queries will regress, meaning, they'll get a different execution plan that causes them to run slower. You can run the regression report to see those queries. You can then force the old plans on to those queries while you go through the task of tuning them (because they almost always need to be tuned, they probably needed tuning in 2012 too) as a more permanent solution.

    In terms of proving something is faster, the mechanisms are simple. Measure query performance on both sides. Compare.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • nirav.gandhi7

    SSC-Addicted

    Points: 415

    Thank you very much for your time.

    We have taken performance reading of both SQL version and identified whenever we used ROW_NUMBER,Rank() functions;SQL Server 2017 express edition generated different plan and sort operator is taking more time.

    I just one doubt, we are using SQL Server 2017 express edition because of that we are not getting performance.Is there any difference on database engine between SQL Server 2017 express edition and Standard or Enterprise edition?

    • This reply was modified 2 months, 3 weeks ago by  nirav.gandhi7.
  • Grant Fritchey

    SSC Guru

    Points: 395580

    Yeah, there are differences and limits on Express. The core behavior is the same, but the amount of memory it can use and other stuff along those lines are different. You can get better performance out of Standard and you can get better performance from Enterprise over Standard. However, each of these usually requires the addition of CPU and memory to take advantage of added functionality. The core really is the same across all of them. Performance is much more often about the correct indexes, data structures, code and statistics instead of versions or settings or even hardware.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply