locked
After migration to MS SQL server 2017 and IIS10 is annoyimng painful slow RRS feed

  • Question

  • We run complex, stateful, rich transactions ASP.NET MVC 5 web platform online. 

    Currently, we are in the process of migration from old environment MS Server 2012, MS SQL server 2014 and IIS 8.5 (asp.net 4.5.1) to MS Server 2016, MS SQL server 2017 and IIS 10 (asp net 4.6). 

    Unfortunately, after migration, everything working 3x times slower which is painfully annoying to our customers. 

    Can anyone please HELP US? I would appreciate any help and support.  


    Sunday, September 16, 2018 10:09 AM

All replies

  • There could be many causes for the slower performance, given the changes to hardware, OS, SQL Server, IIS. SQL Server.

    SQL Server performance is mostly driven by execution plans so, if queries are running slower, different plans may be the reason. Compare the query plans on the old and new system for differences. If you see suboptimal plans on the new system (assuming the database schema and indexes are the same), you can rule out plan regression due to the new CE with the following as a troubleshooting step:

    USE YourDatabase;
    ALTER DATABASE SCOPED CONFIGURATION
        SET LEGACY_CARDINALITY_ESTIMATION=ON;

    Alternatively, make sure you are on the latest CU and enable optimizer hotfixes so that post RTM fixes are considered:

    USE YourDatabase;
    ALTER DATABASE SCOPED CONFIGURATION
        SET LEGACY_CARDINALITY_ESTIMATION=OFF;
    ALTER DATABASE SCOPED CONFIGURATION
        SET QUERY_OPTIMIZER_HOTFIXES=ON;
    In my experience, queries in need of query and index tuning are the most likely to regress so addressing the root cause in that case would be the best long-term solution.



    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Sunday, September 16, 2018 12:13 PM