0

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 19). 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.

Improve this question
4
  • 2
    Did you compare the query plans between the server versions? Commented Aug 7, 2024 at 11:59
  • Yes I have compared the query plans and its different between two versions. in 2017 the query is mostly Waiting for Memory and I/O's Commented Aug 7, 2024 at 12:15
  • 1
    Why not just use the SQL Server 2016 compatibility level instead? Commented Aug 7, 2024 at 14:52
  • 3
    Please post both actual execution plans via Paste The Plan. Commented Aug 7, 2024 at 17:08

1 Answer 1

Reset to default
-1

Your situation shows that There are a missing Index in SQL Server 2017 query related tables.

Additional memory and cache for the server cant solve the problem.

During upgrade process from Sql 2016 to Sql 2017 you could bypass or drop an important Index because of a conflict.

If you know the tables of the Cognos report then compare the table's indexes by using MSSMS>Server/Instance>Databases>Database>Tables>Table>Indexes

If you don't know which tables are using by the Cognos report then

  1. Run Sql Profiler > New Trace

  2. Run Cognos Report for a few minutes then stop

  3. Check Sql Profiler which tables are using by the report

  4. Compare indexes

If there are additional Index in Sql Server 2016 then on the Index>Right Click> Script Index As>Create To>New Query Editor

Copy the generated query and run it in Sql Server 2017 to add the Index.

Then run the report again.

Also optimize the related Indexes and update the statistics of the report tables.

Note: I cant add a comment due to reputation limit. So I wrote an answer

Improve this answer
3
  • There is nothing in the question indicating the problem is a missing Index. Changing the database compatibility level does not cause indexes to be dropped Commented Aug 9, 2024 at 10:50
  • @Martin Smith. "While upgrading our application," the SQL Server compatibility level was updated from SQL Server 2016 (130) to SQL Server 2017 (140) (SSMS 19). Commented Aug 9, 2024 at 13:57
  • @Martin Smith. "While upgrading our application," the SQL Server compatibility level was updated from SQL Server 2016 (130) to SQL Server 2017 (140) (SSMS 19). Questioner doesnt mention just to change the Sql Server compatibility level. Question includes a Cognos Reporting application upgrade. Sql databases coul almost be same but there would be missing index. If they list tables and indexes we will find the answer. Commented Aug 9, 2024 at 14:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.