locked
Performance Issues:Sql server migration from 2008 to 2017 (OS windows 2008 to Windows 2016) RRS feed

  • General discussion

  • Hi,

    We upgraded the db from 2008  to  2017 and we found performance degrade of query and procedures.

    When we change the compatibility of 2017  from 140  to 110 we found some increase in performance.

    As I am new to sql server DB

    Kindly advise

    1) what are the ways we can check the performance of sql server (  Oracle we check by running statistics,awr.. do we have any concept like that?

    Please advise

    Supersent


    Tuesday, January 7, 2020 6:39 AM

All replies

  • I have added option(querytraceon 9481)  at the end of the query to use older compatibility level for the running query  and now the performance looks fine for the compatibility level 140

    is there any other method I can achieve  this?   because I cant change each and every query  in the db for querytraceon 9481.

    Kindly help

    supersent

     

    Tuesday, January 7, 2020 9:43 AM
  • Hi

    You can make use of the Query Tuning Assistant in SSMS 18.4 to be able to track query performance before and after changing the DBCompat level.

    This will identify those queries that have regressed and also provide a recommendation to fix the performance until you can rewrite them.

    Refer to this article for the process.


    Martin Cairney SQL Server MVP

    Tuesday, January 7, 2020 1:10 PM
  • While you can enable this trace flag on the server level, you might want to set it on the database level.

    USE [MyDatabaseName]
    GO
    ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = On;
    GO
    

    Tuesday, January 7, 2020 2:49 PM