none
SQL2008 => SQL-Svr2017 RRS feed

  • Question

  • Upgraded to SQL Server 2017...

    Added to Query: OPTION (USE HINT('QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_100'))

    I assume this tells the optimizer to use the SQL2008R2 engine instead of SQL2017 for SQL plan creation.

    timing went from 2 hrs to 2 mins.

    Can you explain how this could possibly be?

    PS: this is SQL 2017 running inside a VM, so it's not technically Azure SQL, but come on, let's not quibble...


    Wednesday, May 22, 2019 8:34 PM

All replies

  • Hi VikingExplorer,

    Compatibility level still applies across all SQL Server deployment options for Databases (does not apply to Data Warehouse).

    Please see this blog: Default compatibility level 140 for Azure SQL databases (Link)

    • sql-server-2019 is now in preview (compatibility level 150)

    The blog recommends inserting the T-SQL you added to ensure the script runs in the environment it was designed to run in for reasons you have demonstrated.

    Additional Information:

    ALTER DATABASE (Transact-SQL) Compatibility Level (Link)

    Thursday, May 23, 2019 8:31 PM
    Moderator
  • The possible explanation is you are facing CE regression because of new CE  in SQL Server 2017( actually it came from sql server 2014).

    When you are changing the optimizer code to use that of sql server 2012 and before you get better plan.

    I would suggest

    1. Did you upgraded statistics of all SQL Server databases after migration

    2. Did you made sure SQL Server is patched to latest SP ?

    3 Is this only one query giving issue or all the queries ?


    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

    Saturday, May 25, 2019 5:18 AM
  • Hi,

    The first link to the blog "What is plan regression in SQL Server?" is not works since it has extra "http://" at the end
    https://blogs.msdn.microsoft.com/sqlserverstorageengine/2017/06/09/what-is-plan-regression-in-sql-server/http://
    Instead of 
    https://blogs.msdn.microsoft.com/sqlserverstorageengine/2017/06/09/what-is-plan-regression-in-sql-server/

    Good day VikingExplorer,

    If you want our help on finding the specific issue and improving the performance then we need more information and not just stories about the behavior that the user see.

    We need you to provide the tools to reproduce the issue as close as possible to your case. For this and for general review we will need you to provide the exact query code, DDL+ sample DML. In addition we must see your Execution Plans on both Compatibility levels - we need the full XML and not just the image of the Execution Plan.


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]


    Saturday, May 25, 2019 9:11 AM
    Moderator