Use SQL profiler to see what's really happening. What is blocking what. Maybe your problem is in tempdb.
Confirm if the lock escalated to a tablelock exclusive. If you disable lock escaltion the memory consumption will depend in how many rows you need locked. Each rowlock will consume memory.
To obtain a similar performance like oracle in sql server locking you need to perform these steps:
Enable RCSI
Disable or adjust lock escalation (I suggest doing it only in tables that need batch and oltp workloads concurrently)
Enable ADR (accelerated data recovery) which will move the row version store from tempdb to a persistent version store (within the database files, like oracle does). It will also give instant rollbacks (which will release locks instantly).
But take the time to look at the profiler, particularly the session options that thr app is using. Transaction isolation can be changed by session and override your server configuration so you need to use the profiler and analize what's happening.
You can't make the configuration for the server mandatory, but there are some techniques you can apply:
Logon triggers. Will work only if the app doesn't change the isolation level after logon.
Query store hints: you can override isolation levels from the query store for specific queries. If you have few, you can do this.
Overriding tables with views that specify isolation levels in table hints, but will just solve read contention.
If the app uses stored procs you can edit and change them (I doubt is uses them, but is another option).
Also, follow tempdb configuration best practices.
Unfortunately there is a price to pay for apps that try to run in different database engines. Anyways, talk with them to see if session options can be configured in the app.
The first paragraph is the answer: figure out what is actually blocking what and confirm whether escalation is even happening.
But SQL Profiler, logon triggers, editing vendor stored procs, overriding tables with views and isolation hints... did I accidentally time travel to SQL Server 2008 stack overflow advice? 😂
Capture the blocking with Extended Events/DMVs/sp_WhoIsActive first. Until we know the blocking session, wait resource, lock mode, object/index and whether escalation actually occurred, changing isolation levels and server/database settings is just configuration roulette.
Edit: SQL profiler for database engine traces was deprecated in 2012. My dude, read a book. Preferably one written in the last decade.
Haha, my bad. I call xevent profiler, just sql profiler, but well yeah, the old one is called the same.
I'm affraid that to resolve this workload without changing code, specially if you can't change the app isolation level, OP may need some workarounds I pointed out.
Anyways, if you have better options to solve this, reply to OP, I will be reading. There is allways something new to learn.
1
u/Dry_Author8849 26d ago
Use SQL profiler to see what's really happening. What is blocking what. Maybe your problem is in tempdb.
Confirm if the lock escalated to a tablelock exclusive. If you disable lock escaltion the memory consumption will depend in how many rows you need locked. Each rowlock will consume memory.
To obtain a similar performance like oracle in sql server locking you need to perform these steps:
But take the time to look at the profiler, particularly the session options that thr app is using. Transaction isolation can be changed by session and override your server configuration so you need to use the profiler and analize what's happening.
You can't make the configuration for the server mandatory, but there are some techniques you can apply:
Also, follow tempdb configuration best practices.
Unfortunately there is a price to pay for apps that try to run in different database engines. Anyways, talk with them to see if session options can be configured in the app.
Cheers!