Migration of simple SPs from 120 to 150 COMPATIBILITY_LEVEL issue

Alex S 1 Reputation point
2022-10-03T16:51:18.83+00:00

Migration of simple SPs from 120 to 150 COMPATIBILITY_LEVEL issue.
Execution time for the first time run of any SP is close to 3 min.
After the first execution, it takes ms to repeated execution. 
The execution plan is identical in 120 and 150. Recompile did not help.
SPs are sitting on wait: PAGEIOLATCH_SH for an extensive time.

Server: AWS r6i.16xlarge, io2, IOPS 4000.

Can you please let me know what I can do to eliminate the first-time run issue?

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,816 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,559 questions

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Seeya Xi-MSFT 16,446 Reputation points
    2022-10-04T07:53:05.52+00:00

    Hi @Alex S ,

    Welcome to Microsoft Q&A!
    Please see which combination of these configuration settings yields the best performance with your workload. Here are some relevant, likely combinations:

    • Use the existing database compatibility level of the old version
    • Use the existing database compatibility level of the old version and use the query optimizer hotfixes database-scoped configuration option
    • Use the default native database compatibility level of the new version
    • Use the default native database compatibility level of the new version and use the query optimizer hotfixes database-scoped configuration option
    • Use the default native database compatibility level of the new version and use the legacy cardinality estimator database-scoped configuration option
    • Use the default native database compatibility level of the new version and use the legacy cardinality estimator database-scoped configuration option and use the query optimizer hotfixes database-scoped configuration option

    In addition, Microsoft shipped a free tool called Query Tuning Assistant (QTA) in SSMS 18.0. QTA can guide you through the recommended database compatibility level upgrade process in a wizard-fashion, collecting the baseline workload in Query Store, bumping up the database compatibility level, and then comparing performance with the post-upgrade workload collection. At the end of this process, if performance regressions are detected, rather than moving back to the previously known good plan, the QTA will actually suggest hint-based improvements that can be deployed for individual queries (using plan guides), without having to necessarily move back to the legacy CE. It will also gives you some ideas (indirectly) for how you can modify problematic queries that have CE-related regression issues, when you have that option.

    For more information, please refer to this article: Preventing SQL Server Upgrade Performance Issues

    Best regards,
    Seeya

    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments