Locking comparison SQL Server 2017 versus SQL Server 2022

Paolo Taverna 71

We upgraded from SQL Server 2017 to SQL Server 2022. With SQL Server 2022 we are experiencing a lot more and longer locks.

When long lasting locks happens, a lot of process are getting suspended. As soon that the lock is resolved, the processes are like a waterfall. The CPU consumption goes up to 100%, everything becomes slow.

The only way out is to manually stop the long running Execution or to restart the SQL Server instance.

Its a productive environment and it happens twice a week.

Some advise where to start is very appreciated.

Regards

Paolo

Greg Low 1,495 Reputation points Microsoft Regional Director

2024-04-23T00:33:32.74+00:00

Do you know what locks are being held? That's critical to know. The most likely explanation for what you are describing is that the query plans for your queries are different now, and they might be holding locks for longer as the queries are running for longer. Or locks are being escalated to table locks and are blocking other activity.
If you really want to learn about it, we built an online course about it: https://sqldownunder.com/courses/sql-server-locking-blocking

A really good start in your case, would be to open the list of processes in Activity Monitor in SSMS and just see which process is at the head of the blocking chain. Then find out the query that's executing on that process. (You can right-click the process and see details - but you'll get more precise info by querying sys.dm_exec_requests combined with sys.dm_exec_sql_text.

Accepted answer

CosmogHong-MSFT 23,321 Reputation points Microsoft Vendor

2024-04-23T03:16:37.29+00:00

Hi @Paolo Taverna

Besides of Activity Monitor, you could also use SQL Server Locks object counter in Performance Monitor to view current statistics or create a log or alert to monitor locks.

For example, you can monitor Average Wait Time, Number of deadlocks/sec and Lock Timeouts/sec statistics to determine whether there is a problem with resource contention on SQL Server.

Also, you can use the sys.dm_exec_requests DMV to obtain detailed information about the requests currently executing on SQL Server.

See this article for more details: Different techniques to identify blocking in SQL Server.

Best regards,

Cosmog Hong

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".
Please sign in to rate this answer.

0 comments No comments
Sign in to comment

2 additional answers

Paolo Taverna 71 Reputation points

2024-04-23T06:25:34.9733333+00:00

I identified the SQL Statement that are causing the locks. Its the same code we used in the SQL Server 2017. Might be a valid explanation, that the Statements are running longer.

But it feels like the SQL Server 2017 uses a more optimistic locking as the SQL Server 2022
Please sign in to rate this answer.
Erland Sommarskog 101.4K Reputation points MVP

2024-04-23T21:28:07.12+00:00

To my knowing, there are no differences in locking between SQL 2017 and SQL 2022.

As Greg says, issues like these are often due to that query plans changed with the upgrade. About every new version of SQL Server has improvements in query processing, but the nature of optimisation is such that in some cases the change can backfire.

Which compatibility level is your database on?

Paolo Taverna 71 Reputation points

2024-04-24T06:39:32.6733333+00:00

Hi Erland

We are using Compatibility Level 16. Query Store is enabled. I checked the Query Store Reports. A lot of execution plans are showing problems.

The sql code needs rework, i realized it as i analyzed the Extended Event Logs and the Query store reports.

Its just that this code had not such problems with the SQL Server 2017. But i know what you mean. I worked with several SQL Server Versions (6.5, 2000, 2005, 2012, 2017, 2022). Every new SQL Server Version needed some SQL Code refactoring.

It seems that we have to rewrite a lot of sql code.

Thank you for your reply.

Kind regards

Paolo

Erland Sommarskog 101.4K Reputation points MVP

2024-04-24T20:48:42.6333333+00:00

What you could to is to restore compatibility mode 140. Presumably, you will get back the old performance. Run with that for a few days. Then go back to 160 again. Identify the slow queries. Use Query Store to force the old plans to get a temporary solution.

Then you can go and fix the queries.

As for new versions of SQL Server calling for SQL refactoring.. I would put it this way: If you write a bad query with no good index support, but still runs acceptably well on the current SQL Server version, you are not likely to spend time on tuning it. But on next version you may be out of luck.

On the other hand, well-written queries with good index support, are less prone to be affected to optimizer changes. But as I said, if a query runs well, and you are on a tight development schedule, you move on to the next task.

Paolo Taverna 71 Reputation points

2024-04-25T06:23:52.42+00:00

Hi Erland

Thank you for the advice. I will try it. Very curios to see what happens.

Kind regards

Paolo
Sign in to comment
Bruce (SqlWork.com) 56,686 Reputation points

2024-04-23T22:06:29.7833333+00:00
generally with later versions of SQLServer you want to switch to snapshot isolation:

https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/sql/snapshot-isolation-in-sql-server

select name, snapshot_isolation_state_desc from sys.databases
Please sign in to rate this answer.
Paolo Taverna 71 Reputation points

2024-04-24T06:48:45.7366667+00:00

Hi Bruce

As i understand Snapshot Isolation Level has a lot of drawbacks. Its not just "Set isolation level snapshot". Usually we have a Read / Write of 60 / 40.

Read Committed is the level we need.

Thanks for reply

Kind regards

Paolo

Erland Sommarskog 101.4K Reputation points MVP

2024-04-24T20:51:15.74+00:00

Snapshot isolation has both its pros and cons. In many cases, you can get by with setting the database into READ_COMMITTED_SNAPSHOT. This means that READ COMMITTED will use the snapshot, unless you override this with a hint. For a complex system, it can be difficult to analyse whether this can lead to issues.

In this case, true snapshot isolation can be alternative, as you can buy in on snapshot where you deem it safe.

Paolo Taverna 71 Reputation points

2024-04-25T06:27:09.8733333+00:00

Hi Erland

Thank you for replying. Im not trying it. Our Database model has 1300 Tables, more than 60 MB SQL Code (Procs, Trigger, Functions, Views and more).

Read / Write is 60 / 40 and our customers have a lot of users. Im not taking the risk.

Read Commited is still the level i keep using.

Kind regards

Paolo

Greg Low 1,495 Reputation points Microsoft Regional Director

2024-04-25T06:40:43.55+00:00

Read committed snapshot (RCSI) is the best thing that's happened to SQL Server in terms of locking, back in 2005. We always have it on when developing any new apps. I wish you could just turn it on for all existing apps, but you can't. It's actually quite rare to see apps that won't cope with it, but they do exist. While there are other potential issues, every time I've seen a problem, it's been because the app has an existing race condition and RCSI lengthens the period where the race condition can cause issues i.e., it makes an error that could happen anyway, more likely to happen. Of course, the fix for that, is to fix the race conditions that shouldn't be there in the first place.

Are you sure about that 60/40 split? Have you measured it? Are you talking about requests or pages? That write number seems extraordinarily high.

With the original issue though, I still come back to the thing that will have changed is how the query is executed, not how the locking behaviour works.

Erland Sommarskog 101.4K Reputation points MVP

2024-04-25T21:02:29.7533333+00:00

I have also worked with a system of that size of complexity, and we absolutely not turn on RCSI, but that was for very special reasons. (One component that would have succumbed entirely with RCSI.)

For triggers you typically want READCOMMITTEDLOCK, typically if they are doing validation.

But if you have locking problems, you really need to investigate the use of sort of snapshot at that point. "I'm not taking the risk" is not a good argument, when you already have problems. Report procedures are good candidates for snapshot isolation.

Paolo Taverna 71 Reputation points

2024-04-26T11:36:16.8166667+00:00

Hello Erland

Hello Greg

Our application is writing a lot. Our Transctionslogs grow very fast each hour.

Im not taking the risk of snapshot isolation, because we have a standard software and standard database that is installed on 600 customers.

Yes, we have a huge locking problem, but i think that rewriting, optimizing our sql code, is the way that i should go.

I analyzed in deep the extended events logs. Already found a few sql statement that are long running and have high logical reads for a very tiny resultset of a few rows.

I keep you informed how it went.

Kind regards

Paolo
Sign in to comment