0

Investigating significant decrease of my app performance after database migration from Microsoft Sql Server 2019 (15.0.2095.3) to Microsoft Sql Server 2022 (16.0.1000.6). Database is on new machine which is 5y newer, with all benchmarks much better. Database contains exactly the same data/indexes.

After testing / benchmarking hardware, operating system and different queries on both servers this looks like good benchmark which shows where the bottleneck is:

  1. Create 100k of trivial update statements like this:
update RObject set CustomSpeed = 23, AverageSpeed = 9 where Id = 1077752 and SectId = 11009409
  1. Run client statistics from SSMS on Microsoft Sql Server 2019, if we wrap 100k updates in one transaction Total execution time drops from 7s to 4s.

2019

  1. Run client statistics from SSMS on Microsoft Sql Server 2022, if we wrap 100k updates in one transaction Total execution time drops from 595s to 3s.

2022

So something with transactions is slowing it down! When I have 100k transactions vs 1 transaction on 2019 its 2x slower, but when I have 100k transactions vs 1 transaction on 2022 its ~200x slower. Crucial value to watch are "Number of transactions" and "Total execution time". Notice that new machine 25% better without transactions, but with transactions its two orders of magnitude worse. This is crazy.

Some considerations:

  1. IMPLICIT_TRANSACTIONS are off for both.
  2. Query Store was enabled on 2022, but turning it off did nothing.
  3. Delayed durability is disabled on both servers.
  4. Both queries are run on machine where test database located, but if I run again from other machine results are very similar, did that just to eliminate SSMS version / option differences.
  5. Update to 16.0.4003 did not help.
  6. Setting windows server Power Mode to High performance did not help.
  7. Sql 2019 is on Windows Server 2016 and Sql 2022 is on Windows Server 2022.
Thom A
  • 88,727
  • 11
  • 45
  • 75
watbywbarif
  • 6,487
  • 8
  • 50
  • 64
  • 1
    Perhaps https://dba.stackexchange.com rather than SO. – T.J. Crowder Feb 21 '23 at 11:51
  • Installed server 2019 just to be sure, it works even slower, so it must be something with OS / drivers / hw. All hw benchmarks are good, something affecting transactions like some cpu operation missing or not in drivers??? – watbywbarif Mar 01 '23 at 14:41

1 Answers1

0

Although hardware (cpu, mem, disk) benchmarks were good (tried several benchmarks),it seams that something in drivers not being compatible with Windows Server 2022 destroyed performance. After installing Windows 11 on same hardware everything with transactions worked OK.

watbywbarif
  • 6,487
  • 8
  • 50
  • 64