Sql server 2022 transaction performance

Question

Sql server 2022 transaction performance

Andrija Huzjak 16

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:

Create 100k of trivial update statements like this:

update RObject set CustomSpeed = 23, AverageSpeed = 9 where Id = 1077752 and SectId = 11009409

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

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.

User's image

So something with transactions is slowing it down! When I have 100k transactions on 2019 its 2x slower, but when I have 100k transactions on 2022 its ~200x slower. Crucial columns 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:

IMPLICIT_TRANSACTIONS are off for both.
Query Store was enabled on 2022, but turning it off did nothing.
Delayed durability is disabled on both servers.
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.
Update to 16.0.4003 did not help.
Setting windows server Power Mode to High performance did not help.
Sql 2019 is on Windows Server 2016 and Sql 2022 si on Windows Server 2022.

EDIT:

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??? I guess next step will be install older windows server.

EDIT2:

It works great on same hw but with Windows 11. There is some problem with using Windows 11 drivers for Windows Server 2022. Cpu / mem & nvme benchmarks were good, but something is messing with transactions performance. Hardware is ROG STRIX B550-F GAMING + AMD Ryzen 7 5700G.

This may be fixed if official Windows Server 2022 drivers are published for that chipset/cpu.

Andrija Huzjak 16 Reputation points

2023-02-22T10:05:37.3533333+00:00

Notice that new machine (server 2022) has 25% better performance without transactions, but with transactions its two orders of magnitude worse than old machine (server 2019). This is the key to eliminate most of general advice's, this must be some kind of performance issue, or some very specific option which affects it. One other option is that old Intel had some cpu command that new Ryzen does not have, which means world for this test case.

4 answers

Your answer

Andrija Huzjak 16 Reputation points

2023-02-22T10:05:37.3533333+00:00

Notice that new machine (server 2022) has 25% better performance without transactions, but with transactions its two orders of magnitude worse than old machine (server 2019). This is the key to eliminate most of general advice's, this must be some kind of performance issue, or some very specific option which affects it. One other option is that old Intel had some cpu command that new Ryzen does not have, which means world for this test case.

Answer 1

Tom Phillips 17,771

It may not fix your issue, but I highly recommend you update 2022 to a current patch level and test again.

https://learn.microsoft.com/en-US/troubleshoot/sql/releases/sqlserver-2022/cumulativeupdate1

Andrija Huzjak 16 Reputation points

2023-02-22T08:57:03.0833333+00:00

Thx! But already tried this in Some considerations: 5)
Did not help.

Answer 2

Erland Sommarskog 121.7K MVP Volunteer Moderator

Apparently you have some setting on SQL 2019 that you have forgotten to set on your SQL 2022 instance. The difference on SQL 2022 seems a lot closer to what I would expect than the one you have on SQL 2019. Check your trace flags etc.

You can also try a clean install of SQL 2019 and see if you get the same fantastic results as you get on your current instance.

And in any case, wrapping that many single statements in a transaction (or transactions with 1000 statements or so in each) has always been best practice.

Andrija Huzjak 16 Reputation points

2023-02-22T09:01:06.1366667+00:00

Tried to find differences with https://github.com/BrentOzarULTD/SQL-Server-First-Responder-Kit

Tried few things like max server memory but no changes.
Anyhow, this is just a test case where I tried to pinpoint the bottleneck in 2022, performance is degraded on all use cases for server, but hardware is 2-3 generations newer with all benchmarks much better.

Also, we never did specific server tuning as we are mostly using is to shovel lots of data on local network for our own usage, so we use default settings except things like collation and max server memory.

If this is some server option, it would probably be default change from 2019 to 2022. And that option affects transaction performance a lot.

Answer 3

Seeya Xi-MSFT 16,586

Hi @Andrija Huzjak ,

Please read this blog for the directions outlined in it for your reference.

https://blog.sqlauthority.com/2020/02/10/sql-server-top-reasons-for-slow-performance/

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".

Andrija Huzjak 16 Reputation points

2023-02-22T09:45:13.2633333+00:00

This is a nice try, but database structure being the same eliminates almost all suggestions.

Only one section remains: Reason #4: Incorrect Server Deployment
Most of this is already checked, I will try to investigate some further like: High latency for your log files and Single TempDB files.

Except log file latency, which I don't know why it would be bad on better hardware, but will try to check, I don't see how any other thing could be related to transaction performance when benchmarking servers which are not under stress and there is only one workload.

Answer 4

Erland Sommarskog 121.7K MVP Volunteer Moderator

Except log file latency, which I don't know why it would be bad on better hardware, but will try to check,

Well, very apparently there is an issue with your I/O subsystem. Without an explicit transaction, each statement is its own transaction, and SQL Server has to wait for the transaction log to be hardened. You can use sys.dm_exec_session_wait_stats to see which are the top waits for your session. It sounds like you would have a lot of WRITELOG waits.

For what it's worth, I have a demo from one of my sessions, which is good for testing here. This is a loop that performs around 20000 updates, and I have it both with transactions with 1000 rows at a time, and without transactions.

Running directly on my laptop I get this:

SQL 2022 without transactions: 3689 ms.
SQL 2022 with transactions: 3411 ms.
SQL 2019 without transactions: 3230 ms.
SQL 2019 with transactions: 2681 ms.

On this laptop, I also have two VMs, one with SQL 2019 and one with SQL 2022. Here is the data:

SQL 2022 without transactions: 7491 ms.
SQL 2022 with transactions: 3307 ms.
SQL 2019 without transactions: 7506 ms.
SQL 2019 with transactions: 2901 ms.

As you can see that with this complex setup, the cost for single-statement transaction increases considerably.

Andrija Huzjak 16 Reputation points

2023-02-23T10:08:33.0033333+00:00

Hmm, will investigate, but I will need some time, my kid and I are sick so I will have a few days pause.

But your numbers are very interesting, so 2022 is really slower on this kind of tasks. How is this possible, this is very big slowdown even if we ignore transactions. This is not what I expected, if this is not something that can be solved I will probably go back to 2019.
Erland Sommarskog 121.7K Reputation points MVP Volunteer Moderator

2023-02-23T22:23:02.8366667+00:00

Indeed, my demo seems to run somewhat slower on SQL 2022. I have not analysed what this may be due to. But permit me to note that a workload that consists of many small statements can easily take a tool for overhead that is per statement. For instance, on SQL 2022, Query Store is on by default.

The correct solution in my cases is to write a set-based solution where you update many rows at once. (And that is also the point of the presentation where I use this demo.)

In any case, the gross difference you saw on your SQL 2022 machine, must be something in your I/O subsystem.
Andrija Huzjak 16 Reputation points

2023-02-24T09:18:47.86+00:00

If you have time, can you place another query in demo and make transaction for each row, not every 1000 rows. This would prove that this is really some server 2022 problem, not only my system/installation. I would be eternally great-full and maybe MS would do something about this.
Erland Sommarskog 121.7K Reputation points MVP Volunteer Moderator

2023-02-24T15:18:35.3466667+00:00

If you have time, can you place another query in demo and make transaction for each row, not every 1000 rows.

That's the one labeled "without transaction". (That is, when there is no explicit transaction, each statement is its own transaction.)

To me the issue is clear: it's the I/O subsystem of your new server that has a problem. May I venture to guess that this is not a free-standing server with its own disks?

If you really think the SQL Server version has something to do with it, install an instance of SQL 2019 on the new server, and an instance of SQL 2022 on the only server. Be careful to put data and log files on the same disks as in the other instance.
Andrija Huzjak 16 Reputation points

2023-03-01T15:43:43.6133333+00:00

So it is on bare metal, no virtualization, all on the same PC, tried SQL 2019, works bad also. Must be some os / driver stuff. But as it works faster without transaction, it looks like some specific cpu command is missing, something very strange as disk, cpu and mem benchmarks are all great.
Erland Sommarskog 121.7K Reputation points MVP Volunteer Moderator

2023-03-01T21:47:27.8633333+00:00

Bare metal and Direct Attached Storage? Yeah, in that case you have all reason to expect better performance. But it seems that something is causing a high latency.

Did you look at the wait stats?
Andrija Huzjak 16 Reputation points

2023-03-03T16:24:43.4166667+00:00

No, after seeing that there is no issue in Sql server version, I have tried with consumer OS. Windows 11 & Sql server 2019 are good. So I guess there is some issue with motherboard / cpu drivers & windows server 2022. I have excluded that because first thing I tried is cpu / mem / nvme benchmarks and they were OK, much better that old PC. But something there is bad because that hw has no server drivers and windows 10/11 were used.

Share via

Sql server 2022 transaction performance

4 answers

Your answer