upgraded the database from 2012 to 2016 and specific query runs slow



  • I successfully restored a database from SQL Server 2012 to SQL Server 2016. Now some queries are running slow.

    Especially this one.

    SELECT * 
    From DayCalDupLocGradeDayVW A  
    Where A.DistCode = 'NY660900' 
      AND A.SchoolYear = '2022-06-30' 
      AND Exists 
        (Select 1 
         From DayCalDupLocGradeDayVW B 
         Where a.DistCode=b.DistCode 
           AND A.SchoolYear=B.SchoolYear 
           AND A.SchoolDate=B.SchoolDate 
           AND A.LocProgCode=B.LocProgCode 
           AND A.GRADE_LEVEL=B.GRADE_LEVEL 
           AND A.DayType <> B.DayType
        )
    

    When I pass the SchoolYear as 2022 it works fine. I cannot change the query, as it's part of a legacy application. This query works fine in SQL Server 2012. I tried setting the compatibility to low and legacy cardinality to ON, but this was of no help.

    Appreciate your help!



  • Have a look at Microsoft's guidelines as explained in https://docs.microsoft.com/en-us/sql/database-engine/install-windows/upgrade-sql-server?view=sql-server-ver15 (Microsoft | SQL Docs).

    This starter document contains steps to other documents.

    There is a sub document named https://docs.microsoft.com/en-us/sql/database-engine/install-windows/upgrade-database-engine?view=sql-server-ver15 (Microsoft | SQL Docs) which contains a Step 4. which applies to your situation, because you are upgrading - via the backup and restore process - a database from one version to another.

    Step 4. explains:

    Upgrade the https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-compatibility-level?view=sql-server-ver15#compatibility-levels-and-database-engine-upgrades (Applies to: SQL Server and Azure SQL Database). One of the steps to take after your databases are online in the new version of SQL Server or Azure SQL Database, may be to upgrade the databases functionality mode to enable new features, by changing the database compatibility level. This can be done manually or through the Query Tuning Assistant.

    Clicking on the link ( https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-compatibility-level?view=sql-server-ver15#compatibility-levels-and-database-engine-upgrades ) takes you to the ALTER DATABASE... documentation, which has a section explaining:

    Compatibility levels and Database Engine upgrades Database Compatibility Level is a valuable tool to assist in database modernization, by allowing the SQL Server Database Engine to be upgraded, while keeping connecting applications functional status by maintaining the same pre-upgrade Database Compatibility Level. This means that it is possible to upgrade from an older version of SQL Server (such as SQL Server 2008) to SQL Server or Azure SQL Database (including Managed Instance) with no application changes (except for database connectivity). For more information, see Compatibility Certification.

    As long as the application does not need to leverage enhancements that are only available in a higher Database Compatibility Level, it is a valid approach to upgrade the SQL Server Database Engine and maintain the previous Database Compatibility Level. For more information on using compatibility level for backward compatibility, see Compatibility Certification.

    Answering Your Observation

    I successfully restored a database from SQL Server 2012 to SQL Server 2016. Now some queries are running slow.

    You might have to change the Database Compatibility Level either up or down depending on the level of your restored database to achieve better performance.

    ALTER DATABASE  
    SET COMPATIBILITY_LEVEL = 110; 
    

    This will tell the SQL Server engine to treat your database as if it were running on a SQL Server 2012 instance.

    If your database is already running in the compatibility level for SQL Server 2012 which can be queried via:

    SELECT dbs.name, dbs.compatibility_level FROM sys.databases; 
    

    ...then try switching to the new compatibility level:

    ALTER DATABASE  
    SET COMPATIBILITY_LEVEL = 130; 
    

    There are a lot of other settings and trace flags that could assist you in combatting your issues. However, I would recommend consulting a consultant for in-depth analysis or providing more details as suggested in the comments.

    Additional Resources

    The answer to the question https://dba.stackexchange.com/a/286054/15356 goes into some Microsoft recommendations on how to migrate a database.

    Picture of recommended migration steps

    • Upgrade to latest SQL Server
    • Enable Query Store
    • Collect data on workload (Baseline)
    • Set DB compatibility level to latest
    • Fix regressions by forcing good execution plans



Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2