SQL Server Compatibility Issue During Application Upgrade

Question

SQL Server Compatibility Issue During Application Upgrade

Vinodkumar Kamurukuru 0

Dear Microsoft Support Team,

We are encountering a significant performance issue related to SQL Server compatibility during an upgrade of one of our applications. Below are the details of the problem:

Issue Summary: While upgrading our application, the SQL Server compatibility level was updated from SQL Server 2016 (130) to SQL Server 2017 (140) (SSMS 2019 Version). We have observed a substantial increase in query execution time for a specific Cognos report dataset query.

Detailed Description:

Problem: A SQL query that previously executed within 10 minutes on SQL Server 2016 (130) now takes 5-7 hours to complete on SQL Server 2017 (140).

Observations: Execution Plan Analysis:

   - **SQL-2016:** WaitTimeMs = 336, WaitCount = 175,051

         - **SQL-2017:** WaitTimeMs = 16,561, WaitCount = 6,357,099

               - **Analysis:** SQL-2016 shows significantly lower wait times and counts, indicating better memory management.

                  **ASYNC_NETWORK_IO:**

                        - **SQL-2016:** WaitTimeMs = 4,255, WaitCount = 207

                              - **SQL-2017:** WaitTimeMs = 4,283, WaitCount = 141

                                    - **Analysis:** Both sets are similar, but SQL-2016 is marginally better due to slightly lower wait times.

                                       **RESERVED_MEMORY_ALLOCATION_EXT:**

                                             - **SQL-2016:** WaitTimeMs = 2,226, WaitCount = 1,018,040

                                                   - **SQL-2017:** WaitTimeMs = 3,429, WaitCount = 1,353,109

                                                         - **Analysis:** SQL-2016 shows better performance in memory reservation management.

                                                            **PAGEIOLATCH_SH:**

                                                                  - **SQL-2016:** WaitTimeMs = 388, WaitCount = 553

                                                                        - **SQL-2017:** WaitTimeMs = 836, WaitCount = 928

                                                                              - **Analysis:** SQL-2016 performs better for shared page I/O latches.

                                                                                 **Additional Waits in SQL-2017:**

                                                                                       - **LOGMGR_FLUSH:** WaitTimeMs = 88, WaitCount = 219

                                                                                             - **PAGEIOLATCH_UP:** WaitTimeMs = 4, WaitCount = 15

                                                                                                   - **Impact:** These waits are minimal and do not significantly affect overall performance in SQL-2017.

Steps Taken So Far:

Increased both physical memory and cache size to address memory wait times.
Verified and compared execution plans between SQL Server 2016 and SQL Server 2017.

Despite these measures, we have not observed any improvement in the query execution time.

Request: As this query is generated by Cognos and cannot be altered, we seek guidance on database settings or configurations that could optimize performance for this query on SQL Server 2017 (140) compatibility level. Your assistance in identifying and implementing a solution to ensure the query runs efficiently in the upgraded environment would be greatly appreciated.

Thank you for your prompt attention to this matter.

Best regards,Dear Microsoft Support Team,

I hope this message finds you well.

We are encountering a significant performance issue related to SQL Server compatibility during an upgrade of one of our applications. Below are the details of the problem:

Issue Summary: While upgrading our application, the SQL Server compatibility level was updated from SQL Server 2016 (130) to SQL Server 2017 (140) (SSMS 2019 Version). We have observed a substantial increase in query execution time for a specific Cognos report dataset query.

Detailed Description:

Problem: A SQL query that previously executed within 10 minutes on SQL Server 2016 (130) now takes 5-7 hours to complete on SQL Server 2017 (140).

Observations: Execution Plan Analysis:

   - **SQL-2016:** WaitTimeMs = 336, WaitCount = 175,051

         - **SQL-2017:** WaitTimeMs = 16,561, WaitCount = 6,357,099

               - **Analysis:** SQL-2016 shows significantly lower wait times and counts, indicating better memory management.

                  **ASYNC_NETWORK_IO:**

                        - **SQL-2016:** WaitTimeMs = 4,255, WaitCount = 207

                              - **SQL-2017:** WaitTimeMs = 4,283, WaitCount = 141

                                    - **Analysis:** Both sets are similar, but SQL-2016 is marginally better due to slightly lower wait times.

                                       **RESERVED_MEMORY_ALLOCATION_EXT:**

                                             - **SQL-2016:** WaitTimeMs = 2,226, WaitCount = 1,018,040

                                                   - **SQL-2017:** WaitTimeMs = 3,429, WaitCount = 1,353,109

                                                         - **Analysis:** SQL-2016 shows better performance in memory reservation management.

                                                            **PAGEIOLATCH_SH:**

                                                                  - **SQL-2016:** WaitTimeMs = 388, WaitCount = 553

                                                                        - **SQL-2017:** WaitTimeMs = 836, WaitCount = 928

                                                                              - **Analysis:** SQL-2016 performs better for shared page I/O latches.

                                                                                 **Additional Waits in SQL-2017:**

                                                                                       - **LOGMGR_FLUSH:** WaitTimeMs = 88, WaitCount = 219

                                                                                             - **PAGEIOLATCH_UP:** WaitTimeMs = 4, WaitCount = 15

                                                                                                   - **Impact:** These waits are minimal and do not significantly affect overall performance in SQL-2017.

Steps Taken So Far:

Increased both physical memory and cache size to address memory wait times.
Verified and compared execution plans between SQL Server 2016 and SQL Server 2017.

Despite these measures, we have not observed any improvement in the query execution time.

Request: As this query is generated by Cognos and cannot be altered, we seek guidance on database settings or configurations that could optimize performance for this query on SQL Server 2017 (140) compatibility level. Your assistance in identifying and implementing a solution to ensure the query runs efficiently in the upgraded environment would be greatly appreciated.

Thank you for your prompt attention to this matter.

Best regards,

MikeyQiaoMSFT-0444 3,300 Reputation points

2024-08-09T08:31:51.69+00:00

Hi，Vinodkumar Kamurukuru

I am not sure if you are receiving my messages. I am currently rechecking this issue. Has your problem been resolved? To better maintain the thread and make our best efforts to address this issue, we are looking forward to your response. If the problem has been resolved, please select "Answer" and "Accept it," as this will effectively help others who have similar issues.
Deleted

This comment has been deleted due to a violation of our Code of Conduct. The comment was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

2 answers

Your answer

MikeyQiaoMSFT-0444 3,300 Reputation points

2024-08-09T08:31:51.69+00:00

Hi，Vinodkumar Kamurukuru

I am not sure if you are receiving my messages. I am currently rechecking this issue. Has your problem been resolved? To better maintain the thread and make our best efforts to address this issue, we are looking forward to your response. If the problem has been resolved, please select "Answer" and "Accept it," as this will effectively help others who have similar issues.
Deleted

This comment has been deleted due to a violation of our Code of Conduct. The comment was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

Answer 1

Hi，Vinodkumar Kamurukuru

The following link reveals some differences in compatibility between the two.

Differences between compatibility level 130 and level 140

As Erland mentioned, using Query Store can temporarily mitigate compatibility issues.

Keep performance stability during the upgrade to newer SQL Server

It is recommended to upgrade SQL Server to the latest version and re-optimize the queries. Additionally, SQL Server 2017 introduced MAXDOP (Maximum Degree of Parallelism). Set the number of processors to the maximum and see if it helps with the existing queries."

SELECT 
    cpu_count AS LogicalProcessors
FROM 
    sys.dm_os_sys_info;

EXEC sp_configure 'show advanced options', 1; RECONFIGURE; 
EXEC sp_configure 'max degree of parallelism', [your_value];RECONFIGURE;

Best Regards,

Mikey Qiao

If you're satisfied with the answer, don't forget to "Accept it," as this will help others who have similar questions to yours.

Answer 2

This is not the Microsoft Support Team but a peer-to-peer forum. To talk with the support staff, you need to open a support case. However, both your versions are out of mainstream support, so if the issue is due a bug, you will not get a fix.

And, by the way, why are you upgrading to a version that is out of the support? SQL 2019 is still in mainstream support but only for a few more months. The only reasonable version to upgrade to is SQL 2022.

That is not to say that would not face the same problem if you upgraded to SQL 2022. It is certainly not unheard of that you face performance regressions when you move from one compatibility level to another. Microsoft always make changes in the optimizer. They are intended to be improvement, but there are always individual cases when these changes backfire. Often this occurs with queries that have some "problems". That could be poor query constructs on improper indexes. But you were lucky on the old version and did not have to bother, but when you upgraded you ran out of luck.

A good way to deal with this situation is to have Query Store enabled. When you get regressions like this, you can force the old plan through Query Store, at least as a temporary measure until you have the time to make a closer analysis.

If you don't have Query Store enabled for your database, enabled it lower the compat level to 130 and keep it in 130 for a week or two. Then you can switch to 140 and deal with the regressions.

I was not really able to understand that wait-type analysis, but if you are implying that ASYNC_NETWORK_IO is the top wait on SQL 2017, this suggest that this is not a query-execution problem at all. This wait is due to one of two things:

Slow network to connection to the client.
Client is slow to consume the data.

In neither case, SQL Server is the culprit.

Share via

SQL Server Compatibility Issue During Application Upgrade

2 answers

Your answer