Forum Discussion

peterkimani's avatar
peterkimani
Copper Contributor
Jun 22, 2022

how to downgrade sql server 2019 standard to sql server 2014 standard

I have backup sql server database 2014 and restored on 2019 for a production environment. Meanwhile our core appkication which runs on tomcat 8 have extrem slow speeds, hence need to restore database...
bake13's avatar
bake13
Icon for Microsoft rankMicrosoft
Jun 22, 2022

Hi peterkimani -- No, there is no easy way to rollback the database.  But before you do that, what troubleshooting steps have you taken from the database perspective?  I've listed a few ideas below.  Take care.

 

  • Update statistics
  • Change database compatibility level to 120
  • Since the database is now on SQL 2019, enable Query Store and determine which statements are performing poorly.
  • What are the highest wait types?
  • Does excessive index fragmentation exist?
  • peterkimani's avatar
    peterkimani
    Copper Contributor
    Jun 23, 2022

    bake13 

    database compatibility level 

    wait types

    excessive index fragmentation exist

     

    • bake13's avatar
      bake13
      Icon for Microsoft rankMicrosoft
      Jun 23, 2022

      Hi peterkimani -- Thanks for the information.  My recommendation would be to start working through a few of these items.  Index and statistics maintenance should be a high priority.  From there, enable Query Store on the database for  a while to gain an understanding of which queries are performing poorly.

      I don't have context around the wait types, but you may want to consider adjusting max degree of parallelism either at the instance or database level as I noticed CXPACKET was a predominant wait type.

       

      Beyond SQL Server, you didn't mention whether the new SQL2019 instance was on a new server, but I'll assume that is the likely scenario.  Have you used perfmon or other monitoring tool to verify the server is correctly sized, IO throughput is sufficient, etc.?  Take care.

      • peterkimani's avatar
        peterkimani
        Copper Contributor
        Jul 05, 2022

        bake13 

        i was trying to set high affinity for processor but i got below

        TITLE: Microsoft SQL Server Management Studio
        ------------------------------

        Alter failed. (Microsoft.SqlServer.Smo)

        For help, click: https://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=16.100.47008.0&Evtsrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Alter+AffinityInfo&LinkId=20476

        ------------------------------
        ADDITIONAL INFORMATION:

        An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

        ------------------------------

        The affinity mask specified is greater than the number of CPUs supported or licensed on this edition of SQL Server. (Microsoft SQL Server, Error: 5833)

        For help, click: https://docs.microsoft.com/sql/relational-databases/errors-events/mssqlserver-5833-database-engine-error

        BUTTONS:

        OK

         


        -

Resources