locked
Microsoft SQL Server 2012 (SP1) - 11.0.3000.0 (X64) - > Microsoft SQL Server 2012 (SP4) upgrade -> Microsoft SQL Server 2019 standard upgrade RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote


    Hello,

    1.

    Is it ok to upgrade Microsoft SQL Server 2012 EXPRESS(SP1) - 11.0.3000.0 (X64)  - >>>   Microsoft SQL Server 2012 (SP4) 

    without SP2 SP3 SP4 ?

    2.

    Do I must upgrade Microsoft SQL Server 2012 EXPRESS(SP1) to Microsoft SQL Server 2012 (SP4) 

    If I want to restore my current  SQL Server 2012 EXPRESS(SP1)  DB   to   Microsoft SQL Server 2019 64bit standard?

    I take my current  sql express 2012 (sp1) DB and restore them to my new SQL Server 2019 standard version. 

    The result was that  all my querys runs very slow compared with my old sql express 2012 (sp1) SERVER

    Tuesday, June 23, 2020 3:36 PM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote

    1. Service Packs are cumulative, means SP4 include alle patches/fixes of SP2+3; so yes, you can upgrade your instance with SP1 directly to SP4.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Marked as answer by EREZra Tuesday, June 23, 2020 5:12 PM
    Tuesday, June 23, 2020 4:00 PM

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

    1. Service Packs are cumulative, means SP4 include alle patches/fixes of SP2+3; so yes, you can upgrade your instance with SP1 directly to SP4.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Marked as answer by EREZra Tuesday, June 23, 2020 5:12 PM
    Tuesday, June 23, 2020 4:00 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    After you restored your database, did you also re-build all the indexes? Also, did you leave the compatibility level as it was or changed it to the latest?

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    My TechNet articles

    Tuesday, June 23, 2020 5:09 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    After you restored your database, did you also re-build all the indexes? Also, did you leave the compatibility level as it was or changed it to the latest?

    For every expert, there is an equal and opposite expert. - Becker's Law



    Hi, That’s my exact situation!! Yes I did it compatibility 2008 2012 2019 No difference Any idea? Thanks
    Tuesday, June 23, 2020 5:19 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    It is hard to suggest why the queries are slower without seeing them.

    So, you restored your DB in SQL Server 2019 and found your queries performing slower. 

    1. How much slower?

    2. All the queries or only just a few?

    3. What did you do besides restoring (e.g. re-building indexes, etc.)?

    4. Can you show an example of slower query (chose the simplest)? Can you compare execution plans in both servers - are they different?

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    My TechNet articles

    Tuesday, June 23, 2020 5:24 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi, 

    1.70%-80% much slower 

    2.Most of the complex views  

    3.No,

    only restore , re-building indexes , changed  compatibility level 2012 <->2019

    4.

    My old server - SERVER 2012 STANDARD 64 bit - SQL SERVER 2012 express sp1  on HYPER VM

    (50 giga ram + Xeon E5 2407 2.2G  5 cores + 15000rpm SAS DRIVE) 

    My new server - Server 2016 STANDARD 64 bit SQL Server 2019 standard on hyper VM

    (50 giga ram + xeon E5 2609 V4 1.7G  16 cores +15000rpm SAS Drive)  much stronger

    SQL Server 2019 64bit standard supposed to be the most important part of the performance improvement in my situation  
    but No.....

    It`s hard to give example .... I will try 

    Tuesday, June 23, 2020 5:41 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    I think you need to concentrate on one query and compare plans in both servers for the difference for now and find what may be the reason.

    See also

    https://docs.microsoft.com/en-us/sql/relational-databases/user-defined-functions/scalar-udf-inlining?view=sql-server-ver15

    and you may try playing with that setting to see if you can get different performance results.

     

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    My TechNet articles

    Tuesday, June 23, 2020 5:51 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    Hi, I made all my compare tests with the same data , same query... That is the best way to test issues like that, But no results... I see that nobody mention the version issue . Microsoft upgrade demand was minimum sql server 2012 express sp4 (not sp1). Maybe they fix sometimes on SP4 that as connected with my issue? SQL Server 2012 (11.x) SP4 or later https://docs.microsoft.com/en-us/sql/database-engine/install-windows/supported-version-and-edition-upgrades-version-15?view=sql-server-ver15
    Tuesday, June 23, 2020 6:20 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    BTW, take a look at this 

    https://support.microsoft.com/en-us/help/4538581/fix-scalar-udf-inlining-issues-in-sql-server-2019

    May be you need to install CU5 on your SQL 2019.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    My TechNet articles

    Tuesday, June 23, 2020 10:06 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    try using Scalar UDF Inlining   - 

    ALTER DATABASE [WiseERP] SET COMPATIBILITY_LEVEL = 150;
    ALTER DATABASE SCOPED CONFIGURATION SET TSQL_SCALAR_UDF_INLINING = ON;
    No results...

    ______________________

    Try

    SELECT 'EXECUTE sys.sp_refreshsqlmodule ''[' + OBJECT_SCHEMA_NAME(object_id) + +'].' + '[' + OBJECT_NAME(object_id)
           + ']'';' AS 'RefreshStatement'
    FROM sys.sql_modules
    WHERE is_inlineable = 1
          AND inline_type = 1;
    GO

     that effect only on SP ...  

    No results....  

    __________________________________

    Install the latest CU5 SQL 2019 update 

    No results....  

    ___________________________________

    DBCC TRACEON (4199,-1);

    GO

    No results....  

    ___________________________

    Same DB , Same Table , same query ..

    execution time

    Old Server - 9 sec 

    New Server - 35 sec

    total 37,940 rows




    • Edited by EREZra Wednesday, June 24, 2020 8:01 AM
    Wednesday, June 24, 2020 6:41 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Based on the information provided so far it's hard to suggest anything.

    I think you may better start a new thread (either in Transact-SQL or in the Database Engine forum) and provide more details (if possible to provide one of the queries that is slower and execution plans that would be great).

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    My TechNet articles


    • Edited by Naomi N Wednesday, June 24, 2020 1:05 PM
    Wednesday, June 24, 2020 12:57 PM